MYSQL
1) Write an SQL statement to create a table named STUDENTS, with the following
specifications:
Column Name Data Type Constraint
RollNo INT Primary Key
FirstName VARCHAR(20) Not Null
LastName VARCHAR(20) Not Null
DOB DATE
Marks FLOAT
Also, write an SQL query to insert the following data into the STUDENTS table:
1, Supriya, Singh, 2010-08-18, 75.5
Answer:
CREATE TABLE STUDENTS (
RollNo INT PRIMARY KEY,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
DOB DATE,
Marks FLOAT
);
INSERT INTO STUDENTS VALUES
(1, 'Supriya', 'Singh', '2010-08-18', 75.5);
2) Consider the following table EMPLOYEE, which stores EmployeeID, Name,
Department and Salary.
Table: EMPLOYEE
I. Which attribute in the Table can be considered as the Primary Key? Provide
justification for your answer
II. Write a suitable SQL query to add a new column, Experience, of numeric
data type to the table.
Ans:
I. EmployeeID can be considered as Primary Key because it uniquely identifies
each employee in the table.
II. ALTER TABLE Employee ADD Experience INT;
3) Raghav, who works as a database designer, has created a table Student as
shown below:
Table : Student
Write suitable SQL query for the following.
I. Show the Name and City of the students, both in uppercase
II. Display the Student ID along with the name of the month in which the student
was admitted to the school.
III. Calculate and display the average marks obtained by students.
Answers
I. SELECT UPPER(Name), UPPER(City) FROM Student;
II. SELECT StudentID, MONTHNAME(Admission_Date) FROM Student;
III. SELECT AVG(Marks) FROM Student;
4) Consider the following table and write SQL queries for the following:
Table: BOOK
BCODE TITLE AUTHOR PRICE
MIDNIGHT’S
B001 SALMAN RUSHDIE 500
CHILDREN
THE GOD OF SMALL
B002 ARUNDHATI ROY 450
THINGS
B003 A SUITABLE BOY VIKRAM SETH 600
B004 THE WHITE TIGER ARAVIND ADIGA 399
B005 TRAIN TO PAKISTAN KHUSHWANT SINGH 350
I. Display book titles in lowercase.
II. Display the highest price among the books.
III. Display the number of characters in each book title.
IV. Display the Book Code and Price.
Answers
I. SELECT LOWER(TITLE) FROM BOOK;
II. SELECT MAX(PRICE) FROM BOOK;
III. SELECT TITLE, LENGTH(TITLE) AS TitleLength FROM BOOK;
IV. SELECT BCODE, PRICE FROM BOOK;
5) Consider the following table and write SQL queries for the following:
Table: STUDENT
ROLLNO NAME CLASS MARKS
101 RAMESH 10 450
102 SUNITA 10 380
103 KABIR 12 410
104 NEHA 12 490
105 RAHUL 10 360
I. Display all student names in uppercase.
II. Display the average marks of students.
III. Display the roll number and length of each student name of class 10.
Answers
I. SELECT UPPER(NAME) FROM STUDENT;
II. SELECT AVG(MARKS) FROM STUDENT;
III. SELECT ROLLNO, LENGTH(NAME) FROM STUDENT WHERE CLASS=10;
6) Consider the following table and write output of the following SQL queries:
Table: MEDICINE
MID MED_NAME SUPP_CITY STOCK DEL_DATE
M01 PARACETAMOL MUMBAI 200 2023-06-15
M02 AMOXICILLIN KOLKATA 50 2023-03-21
M03 COUGH SYRUP BENGALURU 120 2023-02-10
M04 INSULIN CHENNAI 135 2023-01-25
M05 IBUPROFEN AHMEDABAD 30 2023-04-05
I. SELECT LENGTH(MED_NAME) FROM MEDICINE WHERE STOCK > 100;
II. SELECT MED_NAME FROM MEDICINE WHERE MONTH(DEL_DATE) = 4;
III. SELECT MED_NAME FROM MEDICINE WHERE STOCK BETWEEN 120 AND
200;
IV. SELECT MAX(DEL_DATE) FROM MEDICINE;
Answers
I. Answer: 11, 11, 7 (PARACETAMOL, COUGH SYRUP, INSULIN)
II. Answer: IBUPROFEN
III. Answer: PARACETAMOL, COUGH SYRUP, INSULIN
IV. Answer: 2023-06-15
7) Consider the following table and write output of the following SQL queries:
Table: PRODUCT
PID PNAME CATEGORY PRICE MFG_DATE
P01 LAPTOP ELECTRONICS 55000 2022-01-10
P02 MOBILE ELECTRONICS 30000 2022-03-15
P03 CHAIR FURNITURE 5000 2022-04-12
P04 TABLE FURNITURE 7000 2022-02-18
P05 WATCH ACCESSORY 12000 2022-05-05
I. SELECT LENGTH(PNAME) FROM PRODUCT WHERE PRICE > 10000;
II. SELECT PNAME FROM PRODUCT WHERE MONTH(MFG_DATE) = 4;
III. SELECT PNAME FROM PRODUCT WHERE PRICE BETWEEN 6000 AND
20000;
IV. SELECT MAX(MFG_DATE) FROM PRODUCT;
I. Answer: 6, 6, 5 (LAPTOP, MOBILE, WATCH)
II. Answer: CHAIR
III. Answer: TABLE, WATCH
IV. Answer: 2022-05-05
8) Write suitable SQL query for the following:
I. To extract the first five characters from the product_code column in the
Products table.
II. To display the total number of orders from Order_Id column in the Orders table.
III. To display the year of the order dates from the order_date column in the Orders
table.
IV. To display the Address column from the Customers table after removing
leading and trailing spaces
V. To display the current date.
Answers
I. SELECT LEFT(product_code, 5) FROM Products;
II. SELECT COUNT(Order_Id) FROM Orders;
III. SELECT YEAR(order_date) FROM Orders;
IV. SELECT TRIM(Address) FROM Customers;
V. SELECT DATE(NOW());
9) Write suitable SQL query for the following:
I. To extract the first 3 letters from the Cust_Name column in the Customers
table.
II. To display the highest price from the Price column in the Products table.
III. To display the day of the week from the Order_Date column in the Orders table.
IV. To show the Emp_Name column from the Employee table after replacing
spaces with hyphens.
V. To display the system date and time together.
Answers
I. SELECT LEFT(Cust_Name,3) FROM Customers;
II. SELECT MAX(Price) FROM Products;
III. SELECT DAYNAME(Order_Date) FROM Orders;
IV. SELECT REPLACE(Emp_Name,' ','-') FROM Employee;
V. SELECT NOW();
10) Write suitable SQL query for the following:
I. Write a query to convert the string "ComputerScience" into lowercase letters.
II. Write an SQL query to extract the first 5 characters from the Emp_Name column
of the Employee table.
III. Write a query to find the remainder when the values in the Marks column of the
Students table are divided by 5.
IV. Write an SQL query to display the maximum marks from the Marks column of the
Students table.
V. Write a query to display the minimum price from the Price column of the
Products table.
Answers
I. SELECT LCASE('ComputerScience');
II. SELECT LEFT(Emp_Name,5) FROM Employee;
III. SELECT MOD(Marks,5) FROM Students;
IV. SELECT MAX(Marks) FROM Students;
V. SELECT MIN(Price) FROM Products;
11) Write suitable SQL query for the following:
I. Write a query to display the length of the string "InformationPractice".
II. Write an SQL query to return the middle 3 characters from the Course_Name
column of the Courses table.
III. Write an SQL query to display the total number of employees from the Employee
table.
IV. Write a query to display the average marks obtained from the Marks column of
the Exam table.
Answers
I. SELECT LENGTH('InformationPractice');
II. SELECT MID(Course_Name,3,3) FROM Courses;
III. SELECT COUNT(*) FROM Employee;
IV. SELECT AVG(Marks) FROM Exam;
12) Write suitable SQL query for the following:
I. To display the result of 7 raised to the power 3.
II. To display the string “DATABASE” in lowercase.
III. To display the length of the string “INFORMATION”.
IV. To display the round-off value of 3456.789 up to 2 decimal places.
V. To display the maximum salary from the salary column of the Employees table.
Answers
I. SELECT POWER(7,3);
II. SELECT LOWER('DATABASE');
III. SELECT LENGTH('INFORMATION');
IV. SELECT ROUND(3456.789,2);
V. SELECT MAX(salary) FROM Employees;
