Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Database my SQL codes, Exercises of Database Management Systems (DBMS)

basic codes for basic question in the form of assignment to check the basic knowledge of dbms

Typology: Exercises

2019/2020

Uploaded on 03/22/2020

aditya-makhija
aditya-makhija 🇮🇳

1 document

1 / 5

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
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_
0
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_
0
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_
0
COLLEG
E CITY MARKS
4 Max M COE-3 TIET Goa 20
3 Hela F COE-1 TIET Chandigar 40
pf3
pf4
pf5

Partial preview of the text

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