Download Database my SQL codes and more Exercises Database Management Systems (DBMS) in PDF only on Docsity!
CREATE TABLE Student (Rno NUMBER(10), Names VARCHAR(15), Gender VARCHAR(1), Class_0 VARCHAR(10), College VARCHAR(10), City VARCHAR(10), Marks NUMBER(3) ); INSERT INTO Student VALUES(01,'TIM','M','COE-1','TIET','Patiala',80); INSERT INTO Student VALUES(02,'TOM','M','COE-2','TIET','Amritsar',50); INSERT INTO Student VALUES(03,'Hela','F','COE-1','TIET','Chandigarh',40); INSERT INTO Student VALUES(04,'Max','M','COE-3','TIET','Goa',20); INSERT INTO Student VALUES(05,'Olaf','M','COE-4','TIET','Patiala',65); SELECT *FROM Student RNO NAMES
GENDE
R
CLASS_
COLLEG
E CITY MARKS
1 TIM M COE-1 TIET Patiala 80 2 TOM M COE-2 TIET Amritsar 50 3 Hela F COE-1 TIET Chandigar h 40 4 Max M COE-3 TIET Goa 20 5 Olaf M COE-4 TIET Patiala 65 SELECT *FROM Student WHERE City='Patiala'; RNO NAMES
GENDE
R
CLASS_
COLLEG
E CITY MARKS
1 TIM M COE-1 TIET Patiala 80 5 Olaf M COE-4 TIET Patiala 65 SELECT *FROM Student ORDER BY Marks ASC; RNO NAMES
GENDE
R
CLASS_
COLLEG
E CITY MARKS
4 Max M COE-3 TIET Goa 20 3 Hela F COE-1 TIET Chandigar 40
h 2 TOM M COE-2 TIET Amritsar 50 5 Olaf M COE-4 TIET Patiala 65 1 TIM M COE-1 TIET Patiala 80 UPDATE Student SET marks=89 WHERE Rno=05; SELECT *FROM Student WHERE Rno=05; RNO NAMES GENDER CLASS_0 COLLEGE CITY MARKS 5 Olaf M COE-4 TIET Patiala 89 UPDATE Student SET Rno=09 WHERE Rno=05; SELECT *FROM Student WHERE Rno=09; RNO NAMES
GENDE
R
CLASS_
COLLEG
E CITY MARKS
9 Olaf M COE-4 TIET Patiala 89 UPDATE Student SET Names='Moli', City='Mohali' WHERE Rno=09; SELECT *FROM Student WHERE Rno=09; RNO NAMES
GENDE
R
CLASS_
COLLEG
E CITY MARKS
9 Moli M COE-4 TIET Mohali 89 DELETE FROM Student WHERE City='Amritsar'; SELECT *FROM Student RNO NAMES
GENDE
R
CLASS_
COLLEG
E CITY MARKS
1 TIM M COE-1 TIET Patiala 80 3 Hela F COE-1 TIET Chandigar h 40 4 Max M COE-3 TIET Goa 20 9 Moli M COE-4 TIET Mohali 89 DELETE FROM Student WHERE Marks<30; SELECT *FROM Student RNO NAMES
GENDE
R
CLASS_
COLLEG
E CITY MARKS
1 TIM M COE-1 TIET Patiala 80 3 Hela F COE-1 TIET Chandigar h 40 9 Moli M COE-4 TIET Mohali 89 CREATE TABLE Student (Rno NUMBER(10), Names VARCHAR(15), Gender VARCHAR(1), Class_0 VARCHAR(10), College VARCHAR(10), City VARCHAR(10), Marks NUMBER(3) ); INSERT INTO Student VALUES(01,'TIM','M','COE- 1','TIET','Patiala',80); INSERT INTO Student VALUES(02,'TOM','M','COE- 2','TIET','Amritsar',50); INSERT INTO Student VALUES(03,'Hela','F','COE- 1','TIET','Chandigarh',40);
Where Salary BETWEEN 2000 AND 3000; EMPLOYEE _NO
EMPLOYEE
_NAME
JOB COMISSIO
N
SALARY DEPARTM
ENT_NO
1 Aman Chairman - 3000 10 2 Baba Manager - 2900 10 3 Chinks Manager 1000 2200 20 4 Eric Clerk 800 2210 20 5 Abby Salesperso n
Select * FROM Employee_Table Where Department_No IN (10,20,30); EMPLOYEE_N O
EMPLOYEE_NAM
E JOB COMISSION SALARY DEPARTMENT_NO
1 Aman Chairman - 3000 10 2 Baba Manager - 2900 10 3 Chinks Manager 1000 2200 20 4 Eric Clerk 800 2210 20 5 Abby Salesperson 600 2000 30 6 Aqua Salesperson 400 1900 30 Select Employee_Name FROM Employee_Table WHERE Comission is NULL; EMPLOY EE_NA ME Aman Baba Ace Select Department_No, Salary FROM Employee_Table ORDER BY Department_No, Salary DESC; DEPART MENT_ NO
SALARY
Select Employee_Name FROM Employee_Table WHERE Employee_Name LIKE 'C%'; EMPLOY EE_NA ME Chinks Select Employee_Name FROM Employee_Table WHERE Employee_Name LIKE '%c'; EMPLOYEE_NAM
E
Eric Select Employee_Name FROM Employee_Table WHERE Employee_Name LIKE '%a%a' OR Employee_Name LIKE 'A%a' OR Employee_Name LIKE'A%a%'; EMPLOYEE_NAME Aman Baba Aqua Select Employee_Name FROM Employee_Table WHERE Employee_Name LIKE '_b%'; EMPLOYEE_NAME Abby Select Employee_Name FROM Employee_Table WHERE Employee_Name LIKE 'A%a'; EMPLOYEE_NAME Aqua CREATE TABLE Employee_Table (Employee_No NUMBER(4), Employee_Name VARCHAR (25), Job VARCHAR (30), Comission NUMBER(5), Salary NUMBER(7), Department_No NUMBER(5) ); INSERT INTO Employee_Table VALUES(01,'Aman','Chairman',NULL, 00,'10'); INSERT INTO Employee_Table VALUES(02,'Baba','Manager',NULL, 0,'10'); INSERT INTO Employee_Table VALUES(03,'Chinks','Manager',1000, 00,'20'); INSERT INTO Employee_Table VALUES(04,'Eric','Clerk',800,2210,'20'); INSERT INTO Employee_Table VALUES(05,'Abby','Salesperson',600, 00,'30'); INSERT INTO Employee_Table VALUES(06,'Aqua','Salesperson',400, 900,'30'); INSERT INTO Employee_Table VALUES(07,'Ace','Clerk',NULL,1800,'40' ); Select * FROM Employee_Table Select Employee_No, Employee_Name FROM Employee_Table WHERE Department_No=10; Select Employee_Name FROM Employee_Table WHERE Job= 'Clerk' AND Salary>2000; Select Employee_Name, Salary FROM Employee_Table WHERE Job= 'Clerk' OR Job= 'Salesperson'; Select * FROM Employee_Table Where Salary BETWEEN 2000 AND 3000; Select * FROM Employee_Table Where Department_No IN (10,20,30); Select Employee_Name FROM Employee_Table WHERE Comission is NULL; Select Department_No, Salary FROM Employee_Table ORDER BY Department_No, Salary DESC; Select Employee_Name FROM Employee_Table WHERE Employee_Name LIKE 'C%'; Select Employee_Name FROM Employee_Table WHERE Employee_Name LIKE '%c'; Select Employee_Name FROM Employee_Table WHERE Employee_Name LIKE '%a%a' OR Employee_Name LIKE 'A%a' OR Employee_Name LIKE'A%a%'; Select Employee_Name FROM Employee_Table WHERE Employee_Name LIKE '_b%'; Select Employee_Name FROM Employee_Table WHERE Employee_Name LIKE 'A%a'; DROP TABLE Employee_Table
