1.
Display the list of employees belonging to department 30:
2. Display the list of employee number and name of managers:
3. Display the list of clerks working in department 10:
SELECT * FROM emp WHERE job = 'CLERK' AND deptno = 10;
4. Display detailed list of employees who joined before July 1995:
SELECT * FROM emp WHERE hiredate < '1995-07-01';
5. Display the names of employees who are not managers:
SELECT ename FROM emp WHERE job!=’Manager’;
6. Display list of employees whose numbers are 7438, 7216, 7019, 7984:
SELECT * FROM emp WHERE empno IN (7438, 7216, 7019, 7984);
7. Display employee name and salary where salary is between 9000 and 10500:
SELECT ename, sal FROM emp WHERE sal BETWEEN 9000 AND 10500;
8. Display employee names who joined after 30 June 1995:
SELECT ename FROM emp WHERE hiredate > '1995-06-30';
9. Display the list of different jobs available in emp table:
SELECT DISTINCT job FROM emp;
10. Display employees who are not getting/eligible commission:
SELECT * FROM emp WHERE comm IS NULL OR comm = 0;
11. Display employees whose name starts with “M”:
SELECT * FROM emp WHERE ename LIKE 'M%';
12. Display employees whose name has 6 characters:
SELECT * FROM emp WHERE LENGTH(ename) = 6;
13. Display employees having 'a' as second character:
SELECT * FROM emp WHERE ename LIKE '_a%';
14. Display employees in descending order of salary:
SELECT * FROM emp ORDER BY sal DESC;
15. Display employees in ascending order of hire date:
SELECT * FROM emp ORDER BY hiredate ASC;
17. Display the unique jobs available in emp table:
SELECT DISTINCT job FROM emp;
19. Create new table NewEmp from existing emp table:
CREATE TABLE NewEmp AS SELECT * FROM emp;
20. Add a new column address and mobno to the NewEmp table:
ALTER TABLE NewEmp ADD address VARCHAR(100);
ALTER TABLE NewEmp ADD mobno VARCHAR(10);
21. Make empno a Primary Key and deptno a Foreign Key:
ALTER TABLE emp ADD CONSTRAINT pk_empno PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno);
22. Change employee name to your name for empno = 7034:
UPDATE NewEmp SET ename = 'YourName' WHERE empno = 7034;
23. Change employee name to your friend’s name for empno = 7550:
UPDATE NewEmp SET ename = 'FriendName' WHERE empno = 7550;
24. Insert mobile no and address in your and your friend’s records:
UPDATE NewEmp SET mobno = '9876543210', address = 'Your Address' WHERE empno = 7034;
UPDATE NewEmp SET mobno = '9123456780', address = 'Friend Address' WHERE empno = 7550;
25. Delete the column address from NewEmp table:
ALTER TABLE NewEmp DROP COLUMN address;
26. Delete the NewEmp table:
DROP TABLE NewEmp;
1. Display the annual salary of employee. Annual salary is calculated as “sal*12”.
2. Display the ename as Name and salary is calculated as “sal*12”as ‘Annual salary’
3. Show the different departments available in the “emp” table.
4. Distinct salaries of the employees working in the department number 10.
5. Display all the details of those employees of 30 departments who earn more than 5000
6. Display the name and department number of all those employees who are earning
salary between 8000 and 11000 *BETWEEN
7. Display all the employees who work in any of the department number 10, 20, or 40
SELECT * FROM emp WHERE deptno IN (10, 20, 40);
8. Display all those employees whose name whose name ends with 'a', and gets a salary
more than 8500.
9. All employees containing 'ma' as a substring in name.
10. All columns of employees containing 'a' as the second character in their names.
Consider the following table named “Product”, showing details of products being sold in a grocery
shop.
a) Identify the primary key in Product:
✅The Primary Key is: PCode
It uniquely identifies each product in the table.
b) List the Product Code, Product Name, and Price in descending order of
Product Name. If PName is the same, then display in ascending order of price:
SELECT PCode, PName, UPrice FROM Product ORDER BY PName DESC, UPrice ASC;
c) Add a new column Discount to the table Product:
ALTER TABLE Product ADD Discount DECIMAL(10, 2);
d) Calculate the value of the discount as 10% of UPrice if UPrice > 100,
otherwise 0:
UPDATE Product
SET Discount = CASE
WHEN UPrice > 100 THEN UPrice * 0.10
ELSE 0
END;
e) Increase the price by 12% for all products manufactured by Dove:
UPDATE Product SET UPrice = UPrice * 1.12 WHERE Manufacturer = 'Dove';
f) Display the total number of products manufactured by each manufacturer:
SELECT Manufacturer, COUNT(*) AS TotalProducts FROM Product GROUP BY Manufacturer;
Table name Movie
a) Display all the information from the Movie table.
SELECT * FROM Movie;
b) List business done by the movies showing only MID, MovieName, and
Total_Earning (ProdCost + BusiCost).
SELECT MID, MovieName, (ProdCost + BusiCost) AS Total_Earning FROM Movie;
c) List the different categories of movies.
SELECT DISTINCT Category FROM Movie;
d) Find the net profit of each movie showing MID, MovieName, and NetProfit
(BusiCost - ProdCost).
SELECT MID, MovieName, (BusiCost - ProdCost) AS NetProfit FROM Movie;
e) List MID, MovieName, and Cost for all movies with ProdCost > 10,000 and <
1,00,000.
SELECT MID, MovieName, ProdCost FROM Movie WHERE ProdCost > 10000 AND ProdCost < 100000;
f) List details of all movies which fall in the category of Comedy or Action.
SELECT * FROM Movie WHERE Category IN ('Comedy', 'Action');
g) List details of all movies which have not been released yet.
Assuming current date is '2025-05-29' (today), and unreleased movies have a future ReleaseDate:
SELECT * FROM Movie WHERE ReleaseDate > '2025-05-29';