0% found this document useful (0 votes)
59 views11 pagesPractical Material (Database System)
Uploaded by
ProfessorCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
0% found this document useful (0 votes)
59 views11 pagesPractical Material (Database System)
Uploaded by
ProfessorCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
/ 11
write SQL statements:
i)
Answers:
i)
iy
iw
Show records of all stideni
"Mohammad" and belong to De Keres MATES: start with
_ " G Khan m “Muhammad” or,
jete the record of a student wh: 2
Ose name contains either “Ahmad” or ". e.
Add the new record with the following infSrmation: nee
RollNo = 12, Name = Kashif Mahmoo :
. Mahmood, Ni
Session 2020-2021, Mobile =032 14206455 en
Abdul Majeed, Class = ACS,
ultan.
+ City =
A student's record was en 5
: Ww tered incorrectly whose roll m wi
his/her record with the following information: a eee
Name = Hassan Naseem, FName = Muhammad N: i
s . FD d ad Naseem, Class = BSCS, S
= 2020-2023. Mobile= = 03334709706, City = Sahiwal. a
Following is the SQL statement to show records 6f all students whose names
start with "Muhammad" or "Mohammad" and belong to "DG Khan":
SELECT * FROM Student
WHERE Name LIKE ‘Muhammad%’ OR Name LIKE ‘Mohammad%’
AND City = ‘DG Khan’: .
Following is the SQL statement to del
contains either "Ahmad" or "Ahmed’
DELETE FROM Student
WHERE Name LIKE '%Ahmad%' OR Name LIKE ‘%Ahmed%':
e the record of a student whose name
Following is the SQL statement to add the new record with the given
information: / Bs
INSERT INTO Student (RollNo, Name, FName. Class. Session: Mobite#. City)
VALUES (12. ‘Kashif Mahmood’. “Abdul Majeed’ "ACS" 2020-2021".
103214266477", “Multan’};
Following is the SQL statement to update the incorrect
whose roll number is 15, with the given new? :
dent : i
Se amet vibssstl Naseem’, FName = ‘Muhammad Naseem’, 7
ee 2500000;
fii) Following is the SQL statement to get names and departments of all workess
or employees on the project P20:
SELECT Employee.EmpName, Employee.Dept .
FROM Employee, Assign
WHERE Employee.EmpName = Assign.EmpName
AND Assign. Proj# = ‘P20"; ‘ ‘i
iv) Following Is the SQL statement to add a new snpatic set
; Ahmad" with ID "E1020" to the sales departinenes oe
INSERT INTO Employee (EmpID, EmpName, Dept) .,
VALUES (E1020, ‘Hafiz Saeed Ahmad’, 'Sales’);
y) Following is the SQL statement to change the h loyee
is assigned to project "P25" from 100 ip 180,
UPDATE Assign SET Hours_Assigned i :
i = 150
WHERE Proji = ‘P25' AND EmpiD ='E1011';
wpidll”
« 11 C2 Structured Query Language
‘suppose the STUDENT table with sample data is as under:
STUDENT Table ‘
Address
Islamabad
Faisal | Bahawalnagar
Agsa_| Lahore
Write SQL statements: ot
7) Create a STUDENT table with attributes as mentioned above.
ii) Insert given data in the STUDENT table. i 2
iii) Display student names and CGPA only. F }
jy) Display all student names that have more than 3.00 CGPA. - 1
Answers: F
i) Following is the SQL statement to create a STUDENT table with attributes :
name, Address, Class, and CGPS: ¢
; CREATE TABLE Student (Name CHAR (20). Address CHAR (25), Class ¢
CHAR (15), CGPS NUMBER (5,2) ); i
- ii) Following are the SQL statements to insert data in the STUDENT table:
INSERT: INTO Student (Name, Address, Class, CGPA)
VALUES (‘Bilal’, ‘Islamabad’, 'BS-« CS’, 3.65);
INSERT INTO Student (Name, Address, Class, GPA)
VALUES (‘Faisal’, “‘Bahawalnagar’, 'BS-IT', 3.45);
INSERT. INTO Student (Name, Address, Class, CGPA)
VALUES (‘Aqsa’, ‘Lahore’, ‘BS- CS', 3.68);
Il) Following is’ the SQL statement to display student names and CGPA of
STUDENT table: ’
SELECT Name, CGPA FROM Student;
iv) Following is the SQL statement to display ‘all student names who have more
than 3.00 CGPA:
SELECT Name FROM Student WHE!
RE CGPA > 3.00;
1) Write a SOL statement, which creates the EMP table with the following data
type and constraints: 7 i 374
(Data type: Number)
Foreign Key Relationshig
with Dept Table :
(Data type:
Number)
(Data. type:
(Data type:
Character)
Number)
Primary Key
ii) Write SQL statement on the EMP-table which displays the data‘in tay
containing first column. department number (DeptNo), Sceond cola
number of employees in each department whose names start with "Au 4
third column total salary of all such employees in the departrnent, such as,
DepiNo | TotatEmployees. | TotalSalary <
. li) Write SQL statement on the.EMP table which displays the Ename, Salary,
and DeptNo of those employees who earn more than." UMAR". UMAR is an
employee name (Ename) in the EMP. table. .
Answers: ,
i) Following is the SQL statement to. create an‘ EMP ‘table with the above
_ mentioned data type and constraints: ‘
Rei, _ CREATE TABLE EMP (EmpNo NUMBER(5,0) NOT NULL PRIMARY {
KEY, Ename CHAR(20), Salary NUMBER(5,2), DeptNo NUMBER(5,)),
FOREIGN KEY (DeptNo) REFERENCES DEPT(DeptNo)); .*
ii). Following statemerit displays the data in the table containing the fit
column DeptNo, second column number of employees in each department
whose name starts with "A" and third’ column total salary of all such
+ employees in the department, 7 4
SELECT DeptNo, Count(Ename) AS TotalEmployees, Sum(Selary) AS
TotalSalary FROM EMP ; ‘
WHERE (ENamié Like 'A%’)
GROUP BY DepiNo;
iii) Following-is the SQL statement whieh displays the Ename, Salary, a4
DeptNo of those employees who earns more than."UMAR", UMAR is #2:
employee name (Ename) in the EMP table, 7 : j
SELECT Ename, Salary, DeptNo FROM EMP
WHERE Salary > (SELECT Salary FROM EMP WHERE Ename = ‘UMAR!
my
Pa seed tos ede
‘Suppose the STUDENT, RESULT, COURSE, and FACULTY tables with
sample data are as under: \ © Write the following SQL statements: *
i
ii)
iy
iy
Hada [esit |
RESULT Table
COURSE Table ¢
Crs_ID,} Crs_Name | Crs_Hrs
PK) (EK) (FK) |
FACULTY Table | oh, Biss : ;
Fact_1D | oct Name | Specialization | > : .
(PK) cas
1 Riaz Shahid’ | SE_. ee
2 Safia Iikhar _ | Programming
3 Nasir Anmad Programming
©
Write a SQL SELECT statement on, the STUDENT and RESULT tables to
display students’ names and marks who have marks more than 65 in any couirse.
Write 2 SQL‘ SELECT statement on the FACULTY table to display the
Fact_name and Specialization of prograraming only. yi
Write a SQL SELECT statement on the RESULT table to count the number of
Students whose marks have more than 75.
~-Write SQL INSERT statement on the COURSE table to insert a new record with
data values (4, Database System, 4). PM Series @ Database Mang
376 Bere
Answers:
Following is the SQL SELEC
marks from STUDENT and Rl
65 in any course.
SELECT STUDENT.Sid_Name, RESULT.Marks
FROM STUDENT, RESULT
: . WHERE STUDENT.Std_ID = RESULT.Std_ID
AND RESULT.Marks > 65; .
i) Following is the SQL SELECT statement to display, Fact_name and
Specialization of programming only.”
SELECT Fact_Name, Specialization
FROM FACULTY
WHERE Specialisation = ‘Programming’;
lil) ~. Following is the SQL SELECT statement to count the number of students
whose marks have more than 75. ms 7
SELECT COUNT(*) FROM RESULT WHERE Marks > 75; -
+ iv) Following is the SQL INSERT statement on the COURSE table to insert a
new record with data values (4, Database System, 4).
INSERT INTO COURSE(Crs_ID, Crs_Name, Crs_Hrs)
VALUES(4, ‘Database System’, 4);
T statement to display students’ namee
ESULT tables whose marks have more jt
in
eS ene
@ Write a SQL. statement, which creates the following table named
“EMP_PAY" with "Emp_ID" as a primary key, and insert the following
data in the table. Please make sure salary entry should be in the range of 0
to 400000. ; 3
EMP_PAY Table
[ Emp_1D | Emp_Name | BPS_Grade
Ather Asif | 17
Elaaf Toor 18
Rafia Noor
Asif Bukhari
alalalrm] a
Musarrat Amir ctured S2uery Language .
3 377 *
rite a single SQL statement on the given :
Veond maximum salary of an emplon EMP_PAY table which shows the
write @ SQL statement on. the
iv
following table. 7 “n EMP_PAY
table which shows the
Average-Salary
31000
iy answers: ae : His
SQL statements to create the table named "EMP "
0 San PAY" and insert the data
> The following SQL statement creates the table
"EMP PAY
with "Emp_ID" as the primary key: named “EMP. PAY'
CREATETABLEEMP_PAY °° <”
( é a
Bes. Emp_ID’ INT(5) PRIMARY KEY,-
‘Emp_Namé CHAR(25),
BPS_Grade —_INT(6),
Salary INT(6) CHECK (Salary=0 AND Salary <= 400000)
x ; F
y > The following SQL statement inserts five records, (rows) in the
§ “EMP_PAY" table. It also checks that the salary entry should be in the
range of 0 to 400000:
INSERT INTO EMP_PAY (Emp_iD, Emp_Name, BPS_Grade, Salary)
VALUES
(1, ‘Ather Asif, 17, 30000),
(2, ‘Elaaf Toor’, 18, 35000),
(3, 'Rafia Noor’, 17, 32000),
(4, ‘Asif Bukhari’, 19, 40000),
(8, "Musarrat Amir’, 18. 33000); .
(0. Following is the SQL SELECT. statentent to show the second maximum
salary of an employee in the "EMP_PAY™ table. .
* SELECT MAX (Salary) FROM EMP_PAY :
i ” WHERE: Salary NOT IN (SELECT MAX (Salary) FROM EMP_PAY): PM Series t Database Mar
2378
CT. stateinent. th
ae
(ii) Following is the SQL SELECT Sn leas
> “EMP_PAY" table and shows the
SELECT BPS_Grade, AVG(Salary) AS Average-Salary
» FROM EMP_PAY. : :
: «GROUP BY BPS_Grade “4
: WAVING BPS_Grade IN(17,18);
()- Write a SQL statement to create the_ following table namea
"EMPLOYEE". -" , fog E
7 EMPLOYEE Table.
* [Emp_Name | Address | Salary
Waseem __ | Quetta 36000] i
| tmran Islamabad | 45000 :
[Khurram | Karachi ~ | 30000
(i) _” Write a SQL statement to insert data in the EMPLOYEE table as
shown in the.above table,
(iii) Write a SQL statement to display the average salary of employees from
7 the above EMPLOYEE table. _
Answers:
()—_ CREATE TABLE Employee . :
Co: “pet “vs \ ct
Emp_Name CHAR(20),
Address CHAR(25),
: Salary INT(7)
: “Ys ;
1 i). INSERT INTO Employee (Emp_Name, Address, Siar)
ah Mela Pah ce TORO ner re alan NA of yALUES
i‘ (Waseem’, ‘Quetta’, 35000),
Kk " (‘Imran’, ‘Islamabad’, 45000), me: ie
t F wy ‘ . 7
(Khurram Karachi’, 30000); , eco :
f ee : %.
a SELECT AVG (Salary) FROM Employee;
The output of the above statement will be asunder.
ANG (Salar) eins .
36666.67 *
pores ; i ee
@) Write a SQL SELECT: statement
MONTHLY_ SALES table,
- 26-Mar-12 only.
[@. Write a SQL SELECT statement on the following _ given
« MONTHLY_SALES table, which displays customer names who purchase
Shampoo products only. :
(i) Write a SQL SELECT statement on the following — given
MONTHLY_SALES table, which updates soap price 35 instead of 32.
i on’ the following” given
which provides the sum of alf invoices amount of
ie : MONTHLY_SALES Table
‘Cust_ID | Prod_iD | Prod_Name | Price | Quantity | Bill_Total
165 2 *, 330
|
32] 10 320
4 Shampoo 26-Mar-12
: 2. | Soap 26-Mar-12
3 Z| Cream - 65, 1 85 | 26-Mar-12
Shampoo 465 | 27-Mar-12
| 170| 26-Mar-12 |
es| 2 0
Cream . a
_. Answers:
