0% found this document useful (0 votes)
39 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)
39 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 i374
(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 (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 Amirctured 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 °° 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 ofyALUES
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:
You might also like
