Important Questions on MySQL
Ques1.
Assume that you are working in the IT Department of a Creative Art Gallery (CAG), which sells different forms of art creations like Paintings, Sculptures, etc.
The data of Art Creations and Artists are kept in two tables — Articles and Artists respectively.
Following are few records from these two tables:
Table: Articles
| CODE | A_Code | Article | DOC | Price |
|---|---|---|---|---|
| PL001 | A0001 | Painting | 2018-10-19 | 20000 |
| SC028 | A0004 | Sculpture | 2021-01-15 | 16000 |
| QL005 | A0003 | Quilling | 2024-04-24 | 3000 |
Table: Artists
| A_Code | Name | Phone | DOB | |
|---|---|---|---|---|
| A0001 | Roy | 595923 | r@CrAG.com | 1986-10-12 |
| A0002 | Ghosh | 1122334 | ghosh@CrAG.com | 1972-02-05 |
| A0003 | Gargi | 121212 | Gargi@CrAG.com | 1996-03-22 |
| A0004 | Mustafa | 33333333 | Mf@CrAG.com | 2000-01-01 |
Note:
- The tables contain many more records than shown here.
- DOC is the Date of Creation of an Article.
As an employee of CAG, you are required to write the SQL queries for the following:
(i) To display all the records from the Articles table in descending order of Price.
(ii) To display the details of Articles which were created in the year 2020.
(iii) To display the structure of the Artists table.
(iv) To display the names of all artists whose article is ‘Painting’ through Equi Join.
(v) To display the names of all artists whose article is ‘Painting’ through Natural Join.
Show Answers:
(i) SELECT * FROM Articles
ORDER BY Price DESC;
(ii) SELECT * FROM Articles
WHERE YEAR(DOC) = 2020;
(iii) DESCRIBE Artists;
(iv) SELECT Name FROM Artists, Articles
WHERE Artists.A_Code = Articles.A_Code
AND Articles.Article = ‘Painting’;
(v) SELECT Name FROM Artists
NATURAL JOIN Articles
WHERE Article = ‘Painting’;
Ques2:
A table named THEATER, in CINEMA database, has the following structure:
| Field | Type |
|---|---|
| Th_ID | char(5) |
| Name | varchar(15) |
| City | varchar(15) |
| Location | varchar(15) |
| Seats | int |
Write a function Delete_Theatre() to input the value of Th_ID from the user and permanently delete the corresponding record from the table.
Assume the following for Python-Database connectivity:
Host: localhost
User: root
Password: Ex2025
Ques:3
Consider the table ORDERS given below and write the output of the SQL queries that follow:
| ORDNO | ITEM | QTY | RATE | ORDATE |
|---|---|---|---|---|
| 1001 | RICE | 23 | 120 | 2023-09-10 |
| 1002 | PULSES | 13 | 120 | 2023-10-18 |
| 1003 | RICE | 25 | 110 | 2023-11-17 |
| 1004 | WHEAT | 28 | 65 | 2023-12-25 |
| 1005 | PULSES | 16 | 110 | 2024-01-15 |
| 1006 | WHEAT | 27 | 55 | 2024-04-15 |
| 1007 | WHEAT | 25 | 60 | 2024-04-30 |
(i) SELECT ITEM, SUM(QTY) FROM ORDERS GROUP BY ITEM;
(ii) SELECT ITEM, QTY FROM ORDERS WHERE ORDATE BETWEEN ‘2023-11-01’ AND ‘2023-12-31’;
(iii) SELECT ORDNO, ORDATE FROM ORDERS WHERE ITEM = ‘WHEAT’ AND RATE >= 60;
Ques 4: Consider the table Projects given below:
Table: Projects
| P_id | Pname | Language | Startdate | Enddate |
|---|---|---|---|---|
| P001 | School Management System | Python | 2023-01-12 | 2023-04-03 |
| P002 | Hotel Management System | C++ | 2022-12-01 | 2023-02-02 |
| P003 | Blood Bank | Python | 2023-02-11 | 2023-03-02 |
| P004 | Payroll Management System | Python | 2023-03-12 | 2023-06-02 |
Based on the given table, write SQL queries for the following:
(i) Add the constraint, primary key to column P_id in the existing table Projects.
(ii) To change the language to Python of the project whose id is P002.
(iii) To delete the table Projects from MySQL database along with its data.
Show Answers:
(i) ALTER TABLE Projects
ADD CONSTRAINT PK_Projects PRIMARY KEY (P_id);
OR(without named constraints)
ALTER TABLE Projects
ADD CONSTRAINT PRIMARY KEY (P_id);
(ii) UPDATE Projects SET Language = ‘Python’
WHERE P_id = ‘P002’ ;
(iii) DROP TABLE Projects;
Ques 5:
Consider the tables Admin and Transport given below:
Table: Admin
| S_id | S_name | Address | S_type |
|---|---|---|---|
| S001 | Sandhya | Rohini | Day Boarder |
| S002 | Vedanshi | Rohtak | Day Scholar |
| S003 | Vibhu | Raj Nagar | NULL |
| S004 | Atharva | Rampur | Day Boarder |
Table: Transport
| S_id | Bus_no | Stop_name |
|---|---|---|
| S002 | TSS10 | Sarai Kale Khan |
| S004 | TSS12 | Sainik Vihar |
| S005 | TSS10 | Kamla Nagar |
Write SQL queries for the following:
(i) Display the student name and their stop name from the tables Admin and Transport.
Show Answer:
SELECT S_name, Stop_name FROM Admin, Transport
WHERE Admin.S_id = Transport.S_id;
OR
SELECT S_name, Stop_name FROM Admin
NATURAL JOIN Transport;
(ii) Display the number of students whose S_type is not known.
Answer:
SELECT COUNT(*) FROM Admin
WHERE S_type IS NULL;
(iii) Display all details of the students whose name starts with ‘V’.
Answer
SELECT * FROM Admin
WHERE S_name LIKE ‘V%’;
(iv) Display student id and address in alphabetical order of student name, from the table Admin.
Answer:
SELECT S_id, Address FROM Admin
ORDER BY S_name;
Ques 6:
Consider the table ORDERS given below and write the output of the SQL queries that follow:
Table: ORDERS
| ORDNO | ITEM | QTY | RATE | ORDATE |
|---|---|---|---|---|
| 1001 | RICE | 23 | 120 | 2023-09-10 |
| 1002 | PULSES | 13 | 120 | 2023-10-18 |
| 1003 | RICE | 25 | 110 | 2023-11-17 |
| 1004 | WHEAT | 28 | 65 | 2023-12-25 |
| 1005 | PULSES | 16 | 110 | 2024-01-15 |
| 1006 | WHEAT | 27 | 55 | 2024-04-15 |
| 1007 | WHEAT | 25 | 60 | 2024-04-30 |
(i)
SELECT ITEM, SUM(QTY)
FROM ORDERS
GROUP BY ITEM;
Output:
| ITEM | SUM(QTY) |
|---|---|
| RICE | 48 |
| PULSES | 29 |
| WHEAT | 80 |
(ii)
SELECT ITEM, QTY
FROM ORDERS
WHERE ORDATE BETWEEN '2023-11-01' AND '2023-12-31';
Output:
Output:
| ITEM | QTY |
|---|---|
| RICE | 25 |
| WHEAT | 28 |
(iii)
SELECT ORDNO, ORDATE
FROM ORDERS
WHERE ITEM = 'WHEAT' AND RATE >= 60;
Output:
Output:
| ORDNO | ORDATE |
|---|---|
| 1004 | 2023-12-25 |
| 1007 | 2024-04-30 |