Untitled
unknown
plain_text
a month ago
17 kB
2
Indexable
CREATE TABLE Tbl_Worker(
worker_id INT PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30),
salary VARCHAR(40),
joining_date DATETIME,
department VARCHAR(30)
);
INSERT INTO Tbl_Worker(worker_id, first_name, last_name, salary, joining_date, department) VALUES
(1,'John ','Doe ',100000 ,'2014-02-20 09:00:00','HR'),
(2,'Will','Smith',80000,'2014-06-11 09:00:00','Admin'),
(3,'William','James',300000,'2014-02-20 09:00:00','HR'),
(4,'John','Grey',500000,'2014-02-20 09:00:00','Admin'),
(5,'Sarah','Nova',500000 ,'2014-06-11 09:00:00','Admin'),
(6,'anthea','Elizabeth ',200000,'2014-06-11 09:00:00','Account'),
(7,'Medusa ','Elle',75000 ,'2014-02-20 09:00:00','Account'),
(8,'Olive ','Esse',90000,'2014-06-11 09:00:00','Admin');
SELECT*FROM Tbl_Worker;
CREATE TABLE Tbl_Bonus(
worker_ref_id INT,
bonus_date DATETIME,
bonus_amount VARCHAR(20),
FOREIGN KEY (worker_ref_id) REFERENCES Tbl_worker(worker_id)
);
INSERT INTO Tbl_Bonus(worker_ref_id, bonus_date, bonus_amount) VALUES
(1,'2016-02-20 00:00:00',5000),
(2,'2016-11-06 00:00:00', 3000),
(3,'2016-02-20 00:00:00', 4000),
(1,'2016-02-20 00:00:00', 4500),
(2,'2016-11-06 00:00:00', 3500);
SELECT*FROM Tbl_Bonus;
CREATE TABLE Tbl_Title(
worker_ref_id INT,
worker_title VARCHAR(30),
affected_from DATETIME
FOREIGN KEY (worker_ref_id) REFERENCES Tbl_worker(worker_id)
);
INSERT INTO Tbl_Title(worker_ref_id,worker_title,affected_from) VALUES
(1,'Manager','2016-02-20 00:00:00'),
(2,'Executive','2016-11-06 00:00:00'),
(8,'Executive','2016-11-06 00:00:00'),
(5,'Manager','2016-11-06 00:00:00'),
(4,'Asst. Manager','2016-11-06 00:00:00'),
(7,'Executive ','2016-11-06 00:00:00'),
(6,'Lead ','2016-11-06 00:00:00'),
(3,'Lead','2016-11-06 00:00:00');
SELECT*FROM Tbl_Title;
SELECT FIRST_NAME AS WORKER_NAME
FROM Tbl_Worker;
SELECT LEFT(FIRST_NAME, 3) AS FIRST_3_CHARACTERS
FROM Tbl_Worker;
SELECT CHARINDEX('a', FIRST_NAME) AS POSITION_OF_A
FROM Tbl_Worker
WHERE FIRST_NAME = 'Sarah';
SELECT REPLACE(FIRST_NAME, 'a', 'A') AS UPDATED_FIRST_NAME
FROM Tbl_Worker;
SELECT *
FROM Tbl_Worker
ORDER BY FIRST_NAME ASC;
SELECT *
FROM Tbl_Worker
ORDER BY FIRST_NAME ASC, DEPARTMENT DESC;
SELECT *
FROM Tbl_Worker
WHERE FIRST_NAME LIKE '%a%';
SELECT *
FROM Tbl_Worker
WHERE FIRST_NAME LIKE '%a';
SELECT *
FROM Tbl_Worker
WHERE FIRST_NAME LIKE '____h';
SELECT *
FROM Tbl_Worker
WHERE SALARY BETWEEN 100000 AND 500000;
SELECT COUNT(*)
FROM Tbl_Worker
WHERE DEPARTMENT = 'Admin';
SELECT FIRST_NAME, LAST_NAME
FROM Tbl_Worker
WHERE SALARY BETWEEN 50000 AND 100000;
SELECT FIRST_NAME, LAST_NAME, DEPARTMENT, COUNT(*)
FROM Tbl_Worker
GROUP BY FIRST_NAME, LAST_NAME, DEPARTMENT
HAVING COUNT(*) > 1;
SELECT TOP 10 *
FROM Tbl_Worker;
SELECT DISTINCT SALARY
FROM Tbl_Worker
ORDER BY SALARY DESC
OFFSET 4 ROWS FETCH NEXT 1 ROWS ONLY;
SELECT MAX(SALARY) AS FifthHighestSalary
FROM Tbl_Worker
WHERE SALARY NOT IN (
SELECT TOP 4 SALARY
FROM Tbl_Worker
ORDER BY SALARY DESC
);
SELECT TOP (50) PERCENT *
FROM Tbl_Worker;
SELECT DEPARTMENT
FROM Tbl_Worker
GROUP BY DEPARTMENT
HAVING COUNT(*) < 5;
SELECT TOP 1 *
FROM Tbl_Worker
ORDER BY worker_id DESC;
SELECT TOP 1 *
FROM Tbl_Worker
ORDER BY worker_id ASC;
SELECT TOP 5*
FROM Tbl_Worker
ORDER BY worker_id DESC;
SELECT w.first_name, w.department, w.salary
FROM Tbl_Worker w
JOIN (
SELECT department, MAX(salary) AS max_salary
FROM Tbl_Worker
GROUP BY department
) max_salaries
ON w.department = max_salaries.department
AND w.salary = max_salaries.max_salary;
SELECT TOP 3 salary
FROM Tbl_Worker
ORDER BY salary DESC;
SELECT TOP 3 salary
FROM Tbl_Worker
ORDER BY salary ASC;
SELECT department, SUM(CAST(salary AS DECIMAL(10, 2))) AS total_salaries
FROM Tbl_Worker
GROUP BY department;
CREATE DATABASE company;
USE company;
CREATE TABLE EmployeeInfo_tbl(
EmpID INT PRIMARY KEY,
EmpFname VARCHAR(20),
EmpLname VARCHAR(20),
Department VARCHAR(20),
Project VARCHAR(2),
Addresses VARCHAR(30),
DOB DATE,
Gender VARCHAR (2)
);
INSERT INTO EmployeeInfo_tbl(EmpID, EmpFname, EmpLname, Department, Project, Addresses, DOB, Gender) VALUES
(1, 'Sanjay' , 'Mehra', 'HR', 'p1', 'Hyderabad(HYD)','1976-01-12','M'),
(2, 'Ananya' , 'Mishra', 'Admin', 'p2', 'Delhi(DEL) ','1968-02-05','F'),
(3, ' Rohan ' , 'Diwan', 'Account', 'p3', 'Mumbai(BOM) ','1980-01-01','M'),
(4, 'Sonia' , 'Kulkarni', 'HR', 'p1', 'Hyderabad(HYD)','1992-02-05','F'),
(5, 'Ankit' , 'Kapoor', 'Admin', 'p2', 'Delhi(DEL)','1994-03-07','M');
SELECT*FROM EmployeeInfo_tbl;
CREATE TABLE EmployeePosition_tbl(
EmpId INT,
EmpPosition VARCHAR(20),
DateOfJoining DATE,
Salary VARCHAR(20)
FOREIGN KEY (EmpId) REFERENCES EmployeeInfo_tbl(EmpId)
);
INSERT INTO EmployeePosition_tbl(EmpId,EmpPosition,DateOfJoining,Salary) VALUES
(1,' Manager','2022-01-05',500000),
(2,'Executive', '2022-02-05',75000),
(3,'Manager', '2022-01-05',90000),
(1,'Lead ', '2022-02-05',85000),
(2,'Executive', '2022-01-05',300000);
SELECT*FROM EmployeePosition_tbl;
SELECT UPPER(EmpFname) AS EmpName
FROM EmployeeInfo_tbl;
Q-1. write a SQL query to find the details of those salespeople who come from the 'Paris' City or 'Rome' City. Return salesman_id, name, city, commission.
SELECT*FROM Tbl_Salesman
WHERE city IN('Paris', 'Rome');
Q-2. write a SQL query to find the details of the salespeople who come from either 'Paris' or 'Rome'. Return salesman_id, name, city, commission
SELECT*FROM Tbl_Salesman
WHERE city='Paris' OR city='Rome';
. Q-3. write a SQL query to find the details of those salespeople who live in cities other than Paris and Rome. Return salesman_id, name, city, commission.
SELECT*FROM Tbl_Salesman
WHERE city NOT IN('Paris', 'Rome');
Q-4. write a SQL query to retrieve the details of all customers whose ID belongs to any of the values 3007, 3008 or 3009. Return customer_id, cust_name, city, grade, and salesman_id.
SELECT*FROM Tbl_Customer
WHERE customer_id IN (3007, 3008, 3009);
Q-5. write a SQL query to find salespeople who receive commissions between 0.12 and 0.14 (begin and end values are included). Return salesman_id, name, city, and commission.
SELECT*FROM Tbl_Salesman
WHERE commission BETWEEN 0.12 AND 0.14;
Q-6. write a SQL query to select orders between 500 and 4000 (begin and end values are included). Exclude orders amount 948.50 and 1983.43. Return ord_no, purch_amt, ord_date, customer_id, and salesman_id.
SELECT*FROM Tbl_Orders
WHERE purch_amt BETWEEN 500 AND 4000
AND purch_amt NOT IN(948.50, 1983.43);
Q-7. write a SQL query to retrieve the details of the salespeople whose names begin with any letter between 'A' and 'L' (not inclusive). Return salesman_id, name, city, commission.
SELECT*FROM Tbl_Salesman
WHERE name BETWEEN 'A' AND 'L';
Q-8. write a SQL query to find the details of all salespeople except those whose names begin with any letter between 'A' and 'L' (not inclusive). Return salesman_id, name, city, commission.
SELECT*FROM Tbl_Salesman
WHERE name LIKE '[A-L]%';
Q-9. write a SQL query to retrieve the details of the customers whose names begins with the letter 'B'. Return customer_id, cust_name, city, grade, salesman_id.
SELECT*FROM Tbl_Salesman
WHERE name NOT LIKE '[A-L]%';
Q-10. write a SQL query to find the details of the customers whose names end with the letter 'n'. Return customer_id, cust_name, city, grade, salesman_id.
SELECT*FROM Tbl_Customer
WHERE cust_name LIKE 'B%';
Q-11. write a SQL query to find the details of those salespeople whose names begin with ‘N’ and the fourth character is 'l'. Rests may be any character. Return salesman_id, name, city, commission.
SELECT*FROM Tbl_Customer
WHERE cust_name LIKE '%n';
Q-12. write a SQL query to find those rows where col1 contains the escape character underscore ( _ ). Return col1.
SELECT*FROM Tbl_Salesman
WHERE name LIKE 'N___l%';
Q-13. write a SQL query to identify those rows where col1 does not contain the escape character underscore ( _ ). Return col1.
SELECT col1 FROM Tbl_UniqueIdentification
WHERE col1 LIKE '%\_%' ESCAPE '\';
Q-14. write a SQL query to find rows in which col1 contains the forward slash character ( / ). Return col1.
SELECT col1 FROM Tbl_UniqueIdentification
WHERE col1 NOT LIKE '%\_%' ESCAPE '\';
Q-15. write a SQL query to identify those rows where col1 does not contain the forward slash character ( / ). Return col1.
SELECT col1 FROM Tbl_UniqueIdentification
WHERE col1 LIKE '%/%';
Q-16. write a SQL query to find those rows where col1 contains the string ( _/ ). Return col1.
SELECT col1 FROM Tbl_UniqueIdentification
WHERE col1 NOT LIKE '%/%';
Q-17. write a SQL query to find those rows where col1 does not contain the string ( _/ ). Return col1.
SELECT col1 FROM Tbl_UniqueIdentification
WHERE col1 LIKE '%_/%';
Q-18. write a SQL query to find those rows where col1 contains the character percent ( % ). Return col1.
SELECT col1 FROM Tbl_UniqueIdentification
WHERE col1 NOT LIKE '%_/%';
Q-19. write a SQL query to find those rows where col1 does not contain the character percent ( % ). Return col1.
SELECT col1 FROM Tbl_UniqueIdentification
WHERE col1 LIKE '%\%%' ESCAPE '\';
Q-20. write a SQL query to find all those customers who does not have any grade. Return customer_id, cust_name, city, grade, salesman_id.
SELECT* FROM Tbl_Customer
WHERE grade IS NULL;
Q-21. write a SQL query to locate all customers with a grade value. Return customer_id, cust_name,city, grade, salesman_id.
SELECT*FROM Tbl_Customer
WHERE grade IS NOT NULL;
Q-22. write a SQL query to locate the employees whose last name begins with the letter 'D'. Return emp_idno, emp_fname, emp_lname and emp_dept.
SELECT*FROM Tbl_EmpDetails
WHERE EMP_LNAME LIKE 'D%';
Q-1. write a SQL query to calculate total purchase amount of all orders. Return total purchase amount.
SELECT SUM(purch_amt) AS total_amt
FROM Tbl_Orders;
Q-2. write a SQL query to calculate the average purchase amount of all orders. Return average purchase amount.
SELECT AVG(purch_amt) AS avg_amt
FROM Tbl_Orders;
Q-3. write a SQL query that counts the number of unique salespeople. Return number of salespeople.
SELECT COUNT(DISTINCT salesman_id) AS count_salesman
FROM Tbl_Salesman;
Q-4. write a SQL query to count the number of customers. Return number of customers.
SELECT COUNT(DISTINCT customer_id) AS num_of_customer
FROM Tbl_Customer;
Q-5. write a SQL query to determine the number of customers who received at least one grade for their activity.
SELECT COUNT(DISTINCT customer_id) AS num_of_grade
FROM Tbl_Customer
WHERE grade > 1;
Q-6. write a SQL query to find the maximum purchase amount.
SELECT MAX(purch_amt) AS max_purch_amt
FROM Tbl_Orders;
Q-7. write a SQL query to find the minimum purchase amount.
SELECT MIN(purch_amt) AS min_purch_amt
FROM Tbl_Orders;
Q-8. write a SQL query to find the highest grade of the customers in each city. Return city, maximum grade.
SELECT city, MAX(grade) AS max_grade
FROM Tbl_Customer
GROUP BY city;
Q-9. write a SQL query to find the highest purchase amount ordered by each customer. Return customer ID, maximum purchase amount.
SELECT customer_id, MAX(purch_amt) AS max_purch_amt
FROM Tbl_Orders
GROUP BY customer_id;
Q-10. write a SQL query to find the highest purchase amount ordered by each customer on a particular date. Return, order date and highest purchase amount.
SELECT ord_date, MAX(purch_amt) AS per_date_purchAmt
FROM Tbl_Orders
GROUP BY ord_date;
Q-11. write a SQL query to determine the highest purchase amount made by each salesperson on '2012-08-17'. Return salesperson ID, purchase amount.
SELECT salesman_id, MAX(purch_amt) AS salesman_purchAmt
FROM Tbl_Orders
WHERE ord_date = '2012-08-17'
GROUP BY salesman_id;
Q-12. write a SQL query to find the highest order (purchase) amount by each customer on a particular order date. Filter the result by highest order (purchase) amount above 2000.00. Return customer id, order date and maximum purchase amount.
SELECT customer_id, ord_date, MAX(purch_amt) AS particulardate_purchAmt
FROM Tbl_Orders
GROUP BY customer_id, ord_date
HAVING MAX(purch_amt) > 2000.00;
Q-13. write a SQL query to find the maximum order (purchase) amount in the range 2000 - 6000 (Begin and end values are included.) by combination of each customer and order date. Return customer id, order date and maximum purchase amount.
SELECT customer_id, ord_date, MAX(purch_amt) AS max_purch_amt
FROM Tbl_Orders
GROUP BY customer_id, ord_date
HAVING MAX(purch_amt) BETWEEN 2000 AND 6000;
Q-14. write a SQL query to find the maximum order (purchase) amount based on the combination of each customer and order date. Filter the rows for maximum order (purchase) amount is either 2000, 3000, 5760, 6000. Return customer id, order date and maximum purchase amount.
SELECT customer_id, ord_date, MAX(purch_amt) AS max_purch_amt
FROM Tbl_Orders
GROUP BY customer_id, ord_date
HAVING MAX(purch_amt) IN (2000, 3000, 5760, 6000);
Q-15. write a SQL query to determine the maximum order amount for each customer. The customer ID should be in the range 3002 and 3007(Begin and end values are included.). Return customer id and maximum purchase amount.
SELECT customer_id, MAX(purch_amt) AS max_purch_amt
FROM Tbl_Orders
GROUP BY customer_id
HAVING customer_id BETWEEN 3002 AND 3007;
Q-16. write a SQL query to find the maximum order (purchase) amount for each customer. The customer ID should be in the range 3002 and 3007(Begin and end values are included.). Filter the rows for maximum order (purchase) amount is higher than 1000. Return customer id and maximum purchase amount.
SELECT customer_id, MAX(purch_amt) AS max_purch_amt
FROM Tbl_Orders
WHERE customer_id BETWEEN 3002 AND 3007
GROUP BY customer_id
HAVING MAX(purch_amt) > 1000;
Q-17. write a SQL query to determine the maximum order (purchase) amount generated by each salesperson. Filter the rows for the salesperson ID is in the range 5003 and 5008 (Begin and end values are included.). Return salesperson id and maximum purchase amount.
SELECT salesman_id, MAX(purch_amt) AS max_purch_amt
FROM Tbl_Orders
WHERE salesman_id BETWEEN 5003 AND 5008
GROUP BY salesman_id;
Q-18. write a SQL query to count all the orders generated on '2012-08-17'. Return number of orders.
SELECT COUNT(*) AS order_no
FROM Tbl_Orders
WHERE ord_date = '2012-08-17';
Q-19. write a SQL query to count the number of salespeople in a city. Return number of salespeople.
SELECT city, COUNT(DISTINCT salesman_id) AS num_of_salesman
FROM Tbl_Customer
GROUP BY city;
Q-20. write a SQL query to count the number of orders based on the combination of each order date and salesperson. Return order date, salesperson id.
SELECT ord_date, salesman_id, COUNT(*) AS num_of_orders
FROM Tbl_Orders
GROUP BY ord_date, salesman_id;
Q-21. write a SQL query to calculate the average product price. Return average product price.
SELECT AVG(PRO_PRICE) AS avg_pro_price
FROM Tbl_ItemMaster;
Q-22. write a SQL query to count the number of products whose price are higher than or equal to 350. Return number of products.
SELECT COUNT(*) AS num_of_product
FROM Tbl_ItemMaster
WHERE PRO_PRICE >= 350;
Q-23. write a SQL query to compute the average price for unique companies. Return average price and company id.
SELECT PRO_COM, AVG(PRO_PRICE) AS average_price
FROM Tbl_ItemMaster
GROUP BY PRO_COM;
Q-24. write a SQL query to compute the sum of the allotment amount of all departments. Return sum of the allotment amount.
SELECT SUM(DPT_ALLOTMENT) AS total_allotment_amount
FROM Tbl_Department;
Q-25. write a SQL query to count the number of employees in each department. Return department code and number of employees.
SELECT EMP_DEPT, COUNT(EMP_IDNO) AS numb_of_employees
FROM Tbl_EmpDetails
GROUP BY EMP_DEPT;
Editor is loading...
Leave a Comment