AIR UNIVERSITY
DEPARTMENT OF ELECTRICAL & COMPUTER ENGINEERING
EXPERIMENT NO. 4
Lab Title: SQL Functions (Aggregate & Scalar Functions)
Student Name: ASNAN KHAN, RAYAN SALEH Reg. No: 210296, 210293
Objective:
LAB ASSESSMENT:
Attributes Excellent Good Average Satisfactory Unsatisfactory
(5) (4) (3) (2) (1)
Ability to Conduct
Experiment
Ability to assimilate the
results
Effective use of lab
equipment and follows the
lab safety rules
Total Marks: Obtained Marks:
LAB REPORT ASSESSMENT:
Attributes Excellent Good Average Satisfactory Unsatisfactory
(5) (4) (3) (2) (1)
Data Presentation
Experiment Results
Conclusion
Total Marks: Obtained Marks:
Date: Signature:
AIR UNIVERISTY, ISLAMABAD
Department of Electrical & Computer Engineering
DATABASE - LAB
Lab 04 Tasks:
TABLE:
CODE:
CREATE TABLE studentss (
FName VARCHAR(50),
Reg_No INT,
Courses VARCHAR(50),
Course_Code INT,
Offered_By VARCHAR(50),
);
INSERT INTO studentss (FName, Reg_No, Courses, Course_Code, Offered_By)
VALUES ('Ali', 01, 'DIP', 1001, 'Mr. A');
INSERT INTO studentss (FName, Reg_No, Courses, Course_Code, Offered_By)
VALUES('Basit', 02, 'DBMS', 1002, 'Mr. X');
INSERT INTO studentss (FName, Reg_No, Courses, Course_Code, Offered_By)
VALUES('Akram', 03, 'OS', 1003, 'Mr. Y');
INSERT INTO studentss (FName, Reg_No, Courses, Course_Code, Offered_By)
VALUES('Asad', 04, 'DBMS', 1002, 'Mr. X');
INSERT INTO studentss (FName, Reg_No, Courses, Course_Code, Offered_By)
VALUES('Zeeshan', 05, 'DIP', 1001, 'Mr. A');
INSERT INTO studentss (FName, Reg_No, Courses, Course_Code, Offered_By)
VALUES('Muneer', 06, 'OS', NULL, 'Mr. Y');
INSERT INTO studentss (FName, Reg_No, Courses, Course_Code, Offered_By)
VALUES('Shafqat', 07, 'NM', 1004, 'Mr. H');
INSERT INTO studentss (FName, Reg_No, Courses, Course_Code, Offered_By)
VALUES('Ahsan', 08, 'OS', 1003, 'Mr. Y');
INSERT INTO studentss (FName, Reg_No, Courses, Course_Code, Offered_By)
VALUES('Ikram', 09, 'DIP', NULL, 'MR.A');
INSERT INTO studentss (FName, Reg_No, Courses, Course_Code, Offered_By)
VALUES('Hassan', 10, 'DSP', NULL, NULL);
SELECT * FROM studentss
OUTPUT:
1
AIR UNIVERISTY, ISLAMABAD
Department of Electrical & Computer Engineering
Task 1:
Calculate the number of records for the 3rd, 4th and 5th column.
Find distinct number of records for the Course Code=1002 as Total.
Find number of students registered for the course DIP as Total Courses
CODE:
--task#1.1
SELECT COUNT(Courses) AS Course_Count FROM studentss;
SELECT COUNT(Course_Code) AS Course_Code_Count FROM studentss;
SELECT COUNT(Offered_By) AS Offered_By_Count FROM studentss;
--task#1.2
SELECT COUNT(DISTINCT Reg_No) AS Total FROM studentss WHERE Course_Code =
1002;
--task#1.3
SELECT COUNT(*) AS Total_Courses FROM studentss WHERE Courses = 'DIP';
OUTPUT:
Task 2:
Convert the text valued fields in the above table to the lower case and upper case
alphabets.
CODE:
--task#2
SELECT
LOWER(FName) AS FName_Lowercase,
LOWER(Courses) AS Courses_Lowercase,
LOWER(Offered_By) AS Offered_By_Lowercase
FROM studentss;
2
AIR UNIVERISTY, ISLAMABAD
Department of Electrical & Computer Engineering
SELECT
UPPER(FName) AS FName_Uppercase,
UPPER(Courses) AS Courses_Uppercase,
UPPER(Offered_By) AS Offered_By_Uppercase
FROM studentss;
OUTPUT:
Task 3:
Using GROUP BY statement, group the courses for the above table.
CODE:
--task#3
SELECT
Courses,
COUNT(*) AS Number_of_Students
FROM studentss
GROUP BY Courses;
OUTPUT:
3
AIR UNIVERISTY, ISLAMABAD
Department of Electrical & Computer Engineering
Task 4:
Select maximum of the Reg no and smallest valued course code for the above given
table.
CODE:
--task#4
SELECT
MAX(Reg_No) AS Max_Reg_No,
MIN(Course_Code) AS Min_Course_Code
FROM studentss;
OUTPUT:
Task 5:
Find the length of each record for the first column in the above table as MAXIMUM
LENGTH.
CODE:
--task#5
SELECT
FName,
LEN(FName) AS Name_Length
FROM studentss
OUTPUT:
4
AIR UNIVERISTY, ISLAMABAD
Department of Electrical & Computer Engineering
CODE:
CREATE TABLE Orders (
O_Id INT,
OrderDate DATE,
OrderPrice INT,
Customer VARCHAR(100)
);
INSERT INTO Orders (O_Id, OrderDate, OrderPrice, Customer)
VALUES (1, '2008-11-12', 1000, 'Hansen');
INSERT INTO Orders (O_Id, OrderDate, OrderPrice, Customer)
VALUES (2, '2008-10-23', 1600, 'Nilsen');
INSERT INTO Orders (O_Id, OrderDate, OrderPrice, Customer)
VALUES (3, '2008-09-02', 700, 'Hansen');
INSERT INTO Orders (O_Id, OrderDate, OrderPrice, Customer)
VALUES (4, '2008-09-03', 300, 'Hansen');
INSERT INTO Orders (O_Id, OrderDate, OrderPrice, Customer)
VALUES (5, '2008-08-30', 2000, 'Jensen');
INSERT INTO Orders (O_Id, OrderDate, OrderPrice, Customer)
VALUES (6, '2008-10-04', 100, 'Nilsen');
SELECT * FROM Orders
5
AIR UNIVERISTY, ISLAMABAD
Department of Electrical & Computer Engineering
--task 6
SELECT AVG(OrderPrice) AS AverageOrderPrice
FROM Orders;
Output:
Task 7:
Find if the customers "Hansen" or "Nilsen" have a total order of less than 2100 for the
following
CODE:
--task 7
SELECT Customer, OrderPrice
FROM Orders
WHERE OrderPrice > 1800;
OUTPUT:
Task 8:
Find the total sum (total order) of each customer.
CODE:
--task 8
SELECT Customer, SUM(OrderPrice) AS TotalOrder
FROM Orders
GROUP BY Customer;
OUTPUT:
END
