BS Data Science

Session(2024-2028)

Submitted By:
Iqra Abbas
243643
2nd Semester

Submitted To:
Mam. Ayesha Khaliq

Center of Data Science

Government college University,Faisalabad
 LAB MANUAL
Database Management System
1. Show all data of Customer Table?
SQL view:
SELECT*
FROM form;

Output:

2. Show customer number, name and balance?

SQL view:
SELECT [Customer Number],[Customer Name],Balance
FROM form;
Output:

3. Show customer number as ID and street as Address?

SQL view:
 SELECT [Customer Number] AS ID, Street AS Address
FROM form;
Output:

4. Show the Balance of each customer?

SQL view:
SELECT [Customer Name],balance
FROM form;
Output:

5. Show how much amount is paid by each customer?

SQL view:
SELECT [Customer Name],[Amount Paid]
 FROM form;
Output:

6. Show the cities of all customers?

SQL view:
SELECT [Customer Name],City
FROM form;
Output:

7. Add 1000 in the Balance of all customer and show the value ?
SQL view:
SELECT [Customer Name], Balance, Balance+1000 As Increment
FROM form;
 Output:

8. Calculate the 20% of the amount paid and show it as Bonus of the Sales Reps?
SQL view:
SELECT [Amount Paid],[Amount Paid]*0.2 AS Bonus
FROM form;
Output:

9. Show distinct cities of customers?

SQL view:
SELECT DISTINCT City
FROM form;
Output:
10. Show all sales reps numbers?
SQL view:
SELECT [Sales Rep Number]
FROM form;
Output:

11. Write the names of Customers who belong to Oxford?

SQL view:
SELECT [Customer Name]
FROM form
WHERE City='Oxford';
Output:

12. Write Customer Number and Name of the customers whose sale rep number is 44?
SQL view:
SELECT [Customer Number],[Customer Name]
 FROM form
WHERE [Sales Rep Number]=44;
Output:

13. Write names of customers whose balance price is greater than 500?
SQL view:
SELECT [Customer Name]
FROM form
WHERE Balance >500;
Output:

14. What is name and address of customer ID is CM09?

SQL view:
SELECT [Customer Name],Street
FROM form
WHERE [Customer Number]='CM09';
Output:

15. Who belongs to 2200 lawrence?

SQL view:
SELECT [Customer Name]
FROM form
WHERE Street='2200 lawrence';
Output:

16. Show names of all customers whose balance is greater than 200 ?
SQL view:
SELECT [Customer Name] , Balance
FROM Form
 WHERE Balance>200;
Output:

17. Show name and balance of the customers whose sales Rep Number is 44 ?
SQL View:
SELECT [customer name] , Balance
FROM Form
WHERE [sales rep number]=44;
Output:

18. Show name of customer who lives at 3140 Halsted ?

SQL View:
SELECT [Customer name] , Street
FROM Form
WHERE street = '3110 Halsted';
Output:

19. What is balance of Casual by Marie’s ?

SQL View:
SELECT [customer name] , Balance
FROM Form
WHERE [Customer Name] = 'Casual by Marie';
Output:
20. Show name of customer who belong to Ashton ?
SQL View:
SELECT [Customer Name] , City
FROM Form
WHERE City = 'Ashton';
Output:

21. Show all record of customer whose sales rep number is less than 45 ?
SQL View:
SELECT *
FROM Form
WHERE [Sales rep number] < 45;
Output:

22. Show all record of customer whose amount paid is greater than equal to 1700 ?
SQL View:
SELECT *
FROM Form
WHERE [Amount Paid] >= 1700;
Output:

23. Show customer name whose balance is greater than amount paid ?
 SQL View :
SELECT [Customer Name]
FROM Form
WHERE [Amount paid] > [Balance];
Output:

24. Show customer name and city of the customers whose balance is greater than half of
amount paid ?
SQL View:
SELECT [Customer Name],City
From Form
WHERE Balance > (0.5*[Amount Paid]);
Output:

25. Show customer name whose balance is greater than 30% of Amount Paid ?
SQL View:
SELECT [Customer Name]
FROM Form
WHERE Balance > (0.3*[Amount Paid]);
Output:
26. Show customer name who do no belong to Oxford ?
SQL View:
SELECT [Customer Name]
From Form
WHERE City <>'Oxford';
Output:

27. Show all record of customer who do not have 0 balance ?

SQL View:
SELECT*
FROM Form
WHERE Balance <> 0;
Output:
28. Who are the customers whose sales rep is not 44 ?
SQL View:
SELECT [Customer Name]
FROM Form
WHERE [Sales Rep Number] <> 44;
Output:

29. Write the names of Customers who do not belong to Kingston ?

SQL View:
SELECT [Customer Name]
FROM Form
WHERE city <>'Kingston';
Output:
30. Show names of customers whose balance is lower than 50 ?
SQL View:
SELECT [Customer Name]
FROM Form
WHERE Balance <50;
Output:

31. Show cities of customers who paid more than 300 ?

SQL View:
SELECT DISTINCT City
FROM Form
WHERE [Amount Paid] >300;
Output:
32. Show the names of customers who belong to Anderson, Kingston?
SQL View:
SELECT Name
FROM customers
WHERE City IN ('Anderson', 'Kingston');
Output:

33. Show the names of customers whose worker numbers are 303 and 307?
SQL View:
SELECT Name , [Worker Number]
FROM customers
WHERE [Worker Number] IN (303, 307);
Output:

34. Show complete record of customers whose balance is between 29 and 45?
SQL View:
SELECT*
FROM customers
WHERE Balance BETWEEN 29 AND 45;
Output:
35. Show name and address of customers whose amount paid is between 100 and 200?
SQL View:
SELECT Name , Address
FROM customers
WHERE [Amount Paid] BETWEEN 100 AND 200;
Output:

36. Show complete record of Kotas?

SQL View:
SELECT*
FROM Employee
WHERE City = 'Kotas';
Output:

37. Show the complete record of employees whose first name start with ‘m’?
SQL View:
SELECT*
FROM Employee
WHERE [First Name] LIKE 'm*';
Output:
38. Show the complete record of employees whose city name has ‘e’ as second
character?
SQL View:
SELECT*
FROM Employee
WHERE City LIKE '?e*';
Output:

39. Show complete record of the products whose this letter of category is ‘s’?
SQL View:
SELECT*
FROM products
WHERE Category LIKE 'S*';
Output:

40. Show ID, Product Name of the products whose standard cost is between 10 and 15?
SQL View:
SELECT ID ,[Product Name]
FROM products
WHERE StandardCost BETWEEN 10 AND 15;
Output:

41. Show all record of products whose list price is between 20 and 30?
SQL View:
SELECT*
FROM products
WHERE [List Price] BETWEEN 20 AND 30;
Output:
42. Show product name and list price of the products whose ID is 6, 40 and 57?
SQL View:
SELECT [Product Name] , [List Price]
FROM products
WHERE ID IN( 6 ,40 ,57 );
Output:

43. Show product name and list price of the products whose list price is 9.20,12.75 and
34.80?
SQL View:
SELECT [Product Name] , [List Price]
FROM products
WHERE [List Price] IN( 9.20 ,12.75 ,32.80 );
Output:

44. Show First Name and City of Employees whose job titles are Vice President, Sales,
Sales Manager and Sales Coordinator?
SQL View:
SELECT [First Name] , City
FROM employee
WHERE [Job Title] IN ('Vice President','Sales', 'Sales Manager' ,'Sales Coordinator');
Output:

45. Show First Name of employees who belong to the Seattle and Kirkland?
SQL View:
SELECT [FIrst Name] , City
FROM employee
WHERE City IN('Seattle' , 'Kirkland');
 Output:

46. Show the complete record of employees whose first name start with ‘m’?
SQL View:
SELECT *
FROM employee
WHERE [First Name] LIKE 'M*';
Output:

47. Show the complete record of employees whose city name has ‘e’ as second
character?
SQL View:
SELECT*
FROM employee
WHERE City LIKE '?e*';
Output:

48. Show complete record of the products whose last letter of category is ‘s’?
SQL View:
SELECT*
FROM products
WHERE Category LIKE '*s';
Output:
49. Show names and list price of products who do not have any minimum reorder
quantity?
SQL View:
SELECT [Product Name],[Last Price]
FROM products
WHERE MinReorderQuantity IS NULL;
Output:

50. Show complete record of product whose quantity per unit is not available?
SQL View:
SELECT *
FROM products
WHERE QuantityPerUnit IS NULL;
Output:

51. Show product Name and Code of the Product whose standard Cost is less than 10
OR Greater Than 30?
SQL View:
SELECT [Product Name] , [Product Code]
FROM products
WHERE StandardCost <10 OR StandardCost >30 ;
Output:

52. Show product Name and Code , Standard Cost and List Price of the Products whose
standard cost is greater than or equal to 30 and List Price is greater than 70?
SQL View:
SELECT [Product Name] ,[Product Code], StandardCost, [List Price]
FROM products
WHERE StandardCost >= 30 AND [List Price] >70;
Output:
53. Show customer details of the Customers Whose state is WA and their job title is
Owner?
SQL View:
SELECT*
FROM customers
WHERE State ='WA' AND [Job Title] ='Owner';
Output:

54. Show the data of Customer sorted by City?

SQL View:
SELECT*
FROM customers
ORDER BY City;
Output:

55. Show the data of Customer sorted by Job Title?

SQL View:
SELECT*
FROM customers
ORDER BY [Job Title];
Output:

56. Show data of Product Sorted in terms of Standard cost in ascending order?
SQL View:
SELECT *
FROM products
ORDER BY StandardCost ASC;
Output:
57. Show data of Product Sorted in terms of Standard cost in descending order?
SQL View:
SELECT *
FROM products
ORDER BY StandardCost DESC;
Output:

58. Show data of Customer sorter in terms of First name and Last Name?
SQL View:
SELECT*
FROM customers
ORDER BY [First Name] , [Last Name];
Output: