MYSQL PRACTICALS - 2022
Experiment - 1
Objectives: Table “Order” is shown below. Write commands in SQL for (i) to (iv) and output for (v) and (vi).
Questions:
(i) To display names of SalesPersons (without duplicates)
(ii) To list orderedID and respective OrderAmont in descending order of OrderAmount.
(iii) To count the number of orders booked by SalesPerson with names starting with ‘R’.
(iv) To list order ids, order dates and order amounts that were booked after 1st September 2015.
(v) SELECT ORDERID, ORDERAMOUNT FROM ORDER WHERE ORDERAMOUNT BETWEEN 50000 AND 60000.
(vi) SELECT CONCAT (ORDERID, SALESPERSON), LENGTH(SALESPERSON) FROM ORDER;
Ø Solution:
(i) SELECT DISTINCT(SALESPERSON) FROM ORDERS ;
(ii) SELECT ORDERID, ORDERAMOUNT FROM ORDERS ORDER BY ORDERAMOUNT DESC ;
(iii) SELECT SALESPERSON, COUNT(SALESPERSON) FROM ORDERS WHERE SALESPERSON LIKE ‘R%’ GROUP BY SALESPERSON ;
(iv) SELECT ORDERID, ORDERDATE, ORDERAMOUNT FROM ORDERS WHERE ORDERDATE > ‘2015-09-01’ ;
(v) SELECT ORDERID, ORDERAMOUNT FROM ORDER WHERE ORDERAMOUNT BETWEEN 50000 AND 60000.
(vi) SELECT CONCAT (ORDERID, SALESPERSON), LENGTH(SALESPERSON) FROM ORDER;
OUTPUT
(i)
(ii)
(iii)
(iv)
(v)
(vi)
Experiment - 2
Objectives: Consider the table given below. Write commands in SQL for (i) to (viii) and output for (ix) and (x).
Table: Salesperson
Columns SID and DOB contains Sales Person ID and Data of Birth respectively.
Questions:
(i) Write the the data types of SID and DOB columns
(ii) Display names of salesperson and their Salaries who have salaries in the range 30000.00 to 40000.00.
(iii) To list Names, Phone numbers and DOB of Salespersons who were born before 1st November 1992.
(iv) To display Name and Salaries of Salespersons in descending order of salary.
(v) To display areas in which Salespersons are working. Duplicate Areas should not be displayed.
(vi) To display SID, Names along with Salaries increased by 500. (Increase of Rs. 500 is only to be displayed and not to be updated in the table)
(vii) To display Area along with number of Salespersons working in the area.
(viii) To display Names of Salespersons who have the word ‘Kumar’ anywhere in their names.
(ix) SELECT NAME, LENGTH(NAME) FROM SALESPERSON;
(x) SELECT AREA, COUNT(*) FROM SALESPERSON GROUP BY AREA HAVING COUNT(*) > 1;
Solution:
(i) SID - VARCHAR, DOB - DATE
(ii) SELECT NAME, SALARY FROM SALESPERSON WHERE SALARY BETWEEN 30000.00 AND 40000.00 ;
(iii) SELECT NAME, PHONE, DOB FROM SALESPERSON WHERE DOB > ‘1992-11-01’ ;
(iv) SELECT NAME, SALARY FROM SALESPERSON ORDER BY SALARY DESC;
(v) SELECT DISTINCT AREA FROM SALESPERSON ;
(vi) SELECT SID, NAME, (SALARY + 500) AS SALARY FROM SALESPERSON ;
(vii) SELECT AREA, COUNT (*) FROM SALESPERSON GROUP BY AREA ;
(viii) SELECT NAME FROM SALESPERSON WHERE NAME LIKE ‘%KUMAR%’ ;
(ix) SELECT NAME, LENGTH(NAME) FROM SALESPERSON;
(x) SELECT AREA, COUNT(*) FROM SALESPERSON GROUP BY AREA HAVING COUNT(*) > 1;
OUTPUT
(ii)
(iii)
(iv)
(v)
(vi)
(vii)
(viii)
(ix)
(x)
Experiment - 3
Objectives: Consider the table given below. Write the commands in SQL for (i) to (viii) and output for (ix) and (x).
Table: Infant
Questions:
(i) To display the details about the Cot.
(ii) To list the names of items and their unit price that have unit price less than 800 and discount more than 5%.
(iii) To list the names of items and their date of purchase that were purchased after 31st December 2015.
(iv) To display the number of items that have more than 10% as discount.
(v) To display item code and unit price in decreasing order of unit price.
(vi) To increase the unit price of each item by 10% of their unit price.
(vii) To display the highest unit price of items.
(viii) To display the names of items that have ‘Baby’ anywhere in their item names.
(ix) SELECT MID(ITEM, 1,2) FROM INFANT;
(x) SELECT AVG(UNITPRICE) FROM INFANT WHERE DATEPURCHASE > ‘2015-01-01’ ;
Solution:
(i) SELECT * FROM INFANT WHERE ITEM = 'COT' ;
(ii) SELECT ITEM, UNITPRCE FROM INFANT WHERE UNITPRICE < 800 AND DISCOUNT > 5 ;
(iii) SELECT ITEM, DATEPURCHASE FROM INFANT WHERE DATEPURCHASE > '2015-12-31' ;
(iv) SELECT COUNT(*) 'ITEM' FROM INFANT WHERE DISCOUNT > 10 ;
(v) SELECT ITEMCODE, UNITPRICE FROM INFANT ORDER BY UNITPRICE DESC ;
(vi) UPDATE INFANT SET UNITPRICE = (UNITPRICE + (UNITPRICE*0.10)) ;
(vii) SELECT MAX(UNITPRICE) FROM INFANT ;
(viii) SELECT ITEM FROM INFANT WHERE ITEM LIKE '%BABY%' ;
(ix) SELECT MID(ITEM, 1,2) FROM INFANT ;
(x) SELECT AVG(UNITPRICE) FROM INFANT WHERE DATEPURCHASE > ‘2015-01-01’ ;
OUTPUT
(i)
(ii)
(iii)
(iv)
(v)
(vi)
(vii)
(ix)
(x)
Experiment - 04
Objectives: Consider the table given below. Write the commands in SQL for (i) to (v).
Table: EMPL
Questions:
(i) Calculate average salary of all employees listed in table empl.
(ii) Count number of records in table empl.
(iii) Count number of jobs in table empl.
(iv) How many distinct jobs are listed in table empl?
(v) Display maximum salary from table empl.
Solution:
(i) SELECT AVG(SAL) "AVERAGE SALARY" FROM EMPL ;
(ii) SELECT COUNT(*) 'TOTAL RECORD' FROM EMPL ;
(iii) SELECT COUNT(JOB) "JOB COUNT" FROM EMPL ;
(iv) SELECT COUNT(DISTINCT JOB) 'DISTINCT JOB' FROM EMPL ;
(v) SELECT MAX(SAL) 'MAXIMUM SALARY' FROM EMPL ;
OUTPUT
(i)
(ii)
(iii)
(iv)
Experiment - 05
Objectives: Consider the following table named ‘Product’ showing details of products being sold in a grocery shop. Write the queries for que. (i) and output for the que(ii) to (v).
Table: PRODUCT
Questions:
(i) Write SQL query to display the total number of products manufactured be each manufacturer.
(ii) SELECT PNAME, AVG(UPRICE) FROM PRODUCT GROUP BY PNAME;
(iii) SELECT DISTINCT MANUFACTURER FROM PRODUCT;
(iv) SELECT COUNT(DISTINCT PNAME) FROM PRODUCT;
(v) SELECT PNAME, MAX(UPRICE), MIN(UPRICE) FROM PRODUCT GROUP BY PNAME;
Solution:
(i) SELECT MANUFACTURER, COUNT(PCODE) FROM PRODUCT GROUP BY MANUFACTURER ;
(ii) SELECT PNAME, AVG(UPRICE) FROM PRODUCT GROUP BY PNAME;
(iii) SELECT DISTINCT MANUFACTURER FROM PRODUCT;
(iv) SELECT COUNT(DISTINCT PNAME) FROM PRODUCT;
(v) SELECT PNAME, MAX(UPRICE), MIN(UPRICE) FROM PRODUCT GROUP BY PNAME;
OUTPUT
(i)
(iii)
(iv)
(v)
Experiment - 06
Objectives: Consider the following table named ‘EXAM' with details of marks. Write the command of MYSQL for (i) to (iv) and output for (v) to (vii).
Table: EXAM
Questions:
(i) To display all information of the students of humanities in descending order of percentage.
(ii) To display Adno, SName, Percentage and Stream of those students whose name is less than 6 characters long.
(iii) To add another column Bus_Fees with datatype and size as Decimal (8,2).
(iv) To increase percentage by 2% of all the Humanities students.
(v) SELECT COUNT(*) FROM EXAM ;
(vi) SELECT SNAME, PERCENTAGE FROM EXAM WHERE SNAME LIKE "N%" ;
(vii) SELECT ROUND(PERCENTAGE, 0) FROM EXAM WHERE ADNO = 'R005' ;
Solution:
(i) SELECT * FROM EXAM WHERE STREAM = 'HUMANITIES' ORDER BY PERCENTAGE DESC ;
(ii) SELECT ADNO, SNAME, PERCENTAGE, STREAM FROM EXAM WHERE LENGTH(SNAME) < 6 ;
(iii) ALTER TABLE EXAM ADD BUS_FEES DECIMAL(8,2) ;
(iv) UPDATE EXAM SET PERCENTAGE = PERCENTAGE + PERCENTAGE * 0.02 WHERE STREAM = 'HUMANITIES' ;
(v) SELECT COUNT(*) FROM EXAM ;
(vi) SELECT SNAME, PERCENTAGE FROM EXAM WHERE SNAME LIKE "N%" ;
(vii) SELECT ROUND(PERCENTAGE, 0) FROM EXAM WHERE ADNO = 'R005' ;
OUTPUT
(i)
(ii)
(iii)
(iv)
(vii)
Experiment - 07
Objectives: Consider the table supplier given below. Write the commands in MYSQL for (i) to (iv).
Table: SUPPLIER
Questions:
(i) To display name of the products, whose Pname starts with ‘B’ in ascending order of Price.
(ii) To display Supplier code, product name and City of the products whose quantity is less than 150.
(iii) To count distinct City in the table.
(iv) To insert a new row in the table SUPPLIER
110, ‘Bournvita’, ‘ABC’, 170, ‘Delhi’, 40.00
Solution:
(i) SELECT PNAME FROM SUPPLIER WHERE PNAME LIKE 'B%' ORDER BY PRICE;
(ii) SELECT SCODE, PNAME, CITY FROM SUPPLIER WHERE QTY < 150 ;
(iii) SELECT COUNT(DISTINCT CITY) FROM SUPPLIER ;
(iv) INSERT INTO SUPPLIER VALUES(110, 'BOURNVITA', 'ABC', 170, 'DELHI', 40.00);
OUTPUT
(i)
(ii)
(iii)
(iv)
Experiment - 08
Objectives: Consider the table PERSONS given below. Write the commands in MYSQL for (i) to (iv) and output for (v) to (viii).
Table: PERSONS
Questions:
(i) Display the Surname, Firstname and City of people
residing in Udhamwara city.
(ii) Display the Person Ids (PID), Cities and Pincode of
persons in descending order of pincode.
(iii) Display the Firstname and City of all the females
getting Basic Salaries above 40000.
(iv) Display Firstname and Basicsalary of all the
persons whose first name start with ‘G’.
(v) SELECT SURNAME FROM PERSONS WHERE BASICSALARY >=
50000 ;
(vi) SELECT SUM(BASICSALARY) FROM PERSONS WHERE GENDER =
‘F’ ;
(vii) SELECT GENDER, MIN(BASICSALARY) FROM PERSONS GROUP
BY GENDER ;
(viii) SELECT GENDER, COUNT(*) FROM PERSONS GROUP BY
GENDERS ;
Solution:
(i) SELECT SURNAME, FIRSTNAME, CITY FROM PERSONS WHERE
CITY = ‘UDHAMWARA’ ;
(ii) SELECT PID, CITY, PINCODE FROM PERSONS ORDER BY
PINCODE DESC ;
(iii) SELECT FIRSTNAME, CITY FROM PERSONS WHERE GENDER =
‘F’ AND BASICSALARY > 40000 ;
(iv) SELECT FIRSTNAME, BASICSALARY FROM PERSON WHERE
FIRSTNAME LIKE ‘G%’ ;
(v) SELECT SURNAME FROM PERSONS WHERE BASICSALARY >=
50000 ;
(vi) SELECT SUM(BASICSALARY) FROM PERSONS WHERE GENDER =
‘F’ ;
(vii) SELECT GENDER, MIN(BASICSALARY) FROM PERSONS GROUP
BY GENDER ;
(viii) SELECT GENDER, COUNT(*) FROM PERSONS GROUP BY
GENDER ;
OUTPUT
(i)
(ii)
(iii)
(iv)
(v)
(vi)
(vii)
(viii)
Experiment - 09
Objectives: Consider the table DOCTOR given below. Write the commands in MYSQL for (i) to (iv) and output for (v) to (viii).
Table : DOCTOR
Questions
(i) Display the names and dates of joining of doctors of Oncology department.
(ii) Display the names and salaries of doctors in descending order of salaries.
(iii) Display the names and salaries of all the females doctors who are getting salary above 50000.
(iv) Display names of each department along with total salary being given to doctors of that department.
(v) SELECT DEPARTMENT FROM DOCTOR WHERE SALARY > = 55000 ;
(vi) SELECT SUM(SALARY) FROM DOCTORS WHERE DEPARTMENT = 'SURGERY' ;
(vii) SELECT DOC_NAME FROM DOCTOR WHERE DOC_NAME LIKE 'J%' ;
Solutions
(i) SELECT DOC_NAME, DATE_OF_JOIN FROM DOCTOR WHERE DEPARTMENT = 'ONCOLOGY' ;
(ii) SELECT DOC_NAME, SALARY FROM DOCTOR ORDER BY SALARY DESC;
(iii) SELECT DOC_NAME, SALARY FROM DOCTOR WHERE GENDER = 'F' AND SALARY > 50000 ;
(iv) SELECT DEPARTMENT, SUM(SALARY) FROM DOCTOR GROUP BY DEPARTMENT ;
(v) SELECT DEPARTMENT FROM DOCTOR WHERE SALARY > = 55000 ;
(vi) SELECT SUM(SALARY) FROM DOCTORS WHERE DEPARTMENT = 'SURGERY' ;
(vii) SELECT DOC_NAME FROM DOCTOR WHERE DOC_NAME LIKE 'J%' ;
OUTPUT
(i)
(ii)
Experiment - 10
Objectives: Consider the table LIBRARY given below. Write the commands in MYSQL for (i) to (iv) and output for (v) to (viii).
Table : LIBRARY
Questions
(i) To list the BookTitle of FND type.
(ii) To display a report listing BookTitle, Type and Price in descending order of price.
(iii) To count the number of BooksTitle, who have FND type.
(iv) To insert a new row in the table LIBRARY.
7, 'Windows 8 Basics', 'FND', 'McGraw', 7, 150 ;
(v) SELECT AVG(PRICE) FROM LIBRARY WHERE TYPE = 'FND' ;
(vi) SELECT COUNT(DISTINCT TYPE) FROM LIBRARY ;
(vii) SELECT MIN(PRICE) FROM LIBRARY WHERE TYPE = 'FDN' ;
Solution:
(i) SELECT BOOKTITLE FROM LIBRARY WHERE TYPE = 'FND'
(ii) SELECT BOOKTITLE, TYPE, PRICE FROM LIBRARY ORDER BY PRICE DESC ;
(iii) SELECT COUNT(*) FROM LIBRARY WHERE TYPE = 'FND' ;
(iv) INSERT INTO LIBRARY VALUES( 7, 'WINDOWS 8 BASICS', 'FND', 'McGraw', 7, 150) ;
(v) SELECT AVG(PRICE) FROM LIBRARY WHERE TYPE = 'FND' ;
(vi) SELECT COUNT(DISTINCT TYPE) FROM LIBRARY ;
(vii) SELECT MIN(PRICE) FROM LIBRARY WHERE TYPE = 'FND' ;
OUTPUT
(i)
(ii)
===========================
No comments:
Post a Comment
Please do not any spam in the comment box.