Practice Question - MySQL

Introduction

This practice question is designed to test your knowledge of MySQL database querying and schema design. It covers a variety of topics related to database management, including SQL queries, data manipulation, table creation, and indexing. Whether you are a beginner looking to sharpen your skills or an experienced database developer seeking a refresher, this question will help you improve your proficiency in working with MySQL.

• DDL – Database Related commands Questions
• DDL – Table Related commands excluding Alter table Questions
• DDL – Alter Table commands Questions
• DML – INSERT INTO commands questions
• DML – UPDATE and DELETE commands Questions
• DML – SELECT command Questions

DDL – Database Related commands Questions

1. If a database "Employee" exists, which MySql command helps you to start working in that database?

2. Write MySql command will be used to open an already existing database "LIBRARY"?

3. Write MySql command to open an existing database?

4. What does SQL stand for? What is MySQL?

5. Write two examples of DBMS software?

6. Sharmila wants to make the database named ‘COMPANY’ active. Write MySQL commands for it?

7. What is MySQL?

8. What is the relationship between SQL and MySQL?

9. Mention any two example of common Database Management System?

10. Suggest Archana suitable command for the following purpose:

• To display the list of the database already existing in MySQL.
• To use the database named City.
• To remove the pre-existing database named Clients.

8. Write the command to display the name of the active database?

9. Write the command to create a new database “School”?

10. Write the command to create a new database “School”?

DDL – Table Related commands excluding Alter table Questions

1. Write an SQL query to create the table 'Menu' with the following structure?

+-----------+------------------+-------------+
| Field     |     Type         | Constraint  | 
+-----------+------------------+-------------+
| ItemCode  |   Varchar(5)     | Primary Key |  
| ItemName  |   Varchar(20)    |             | 
| Category  |   Varchar(20)    |             | 
| Price     |   Decimal(5, 2)  |             | 
+------------+-----------------+-------------+

2. Can a table have multiple primary keys? Can it have multiple foreign keys?

3. In a Student table, out of Roll Number, Name, Address which column can be set as Primary key and why?

4. Ms. Mirana wants to remove the entire content of a table "BACKUP" alongwith its structure to release the storage space. What MySql statement should she use?

5. Write MySql command to create the Table STOCK including its Constraints?

+--------------------+------------------+-------------+
| Name of Column     |     Type         | Constraint  | 
+--------------------+------------------+-------------+
| Id                 |   Varchar        | Primary Key |  
| Name               |   Varchar        |             | 
| Company            |   Varchar        |             | 
| Price              |   Decimal        | Not Null    | 
+--------------------+------------------+-------------+

6. Write one similarity and one difference between CHAR and VARCHAR data types?

7. Saumya had previously created a table named ‘Product’ in a database using MySQL. Later on she forgot the table structure. Suggest her suitable MySQL command through which she can check the structure of the already created table?

8. Roli wants to list the names of all the tables in her database named ‘Gadgets’. Which command (s) she should use to get the desired result?

9. Name the SQL commands used to:

• Physically delete a table from the database.
• Display the structure of a table.

10. Write one similarity and one difference between UNIQUE and PRIMARY KEY constraints?

11. An attribute A of datatype varchar(20) has the value “Amit” . The attribute B of datatype char(20) has value ”Karanita” . How many characters are occupied in attribute A ? How many characters are occupied in attribute B?

12. Mrs. Sharma is the classteacher of Class ‘XII A’ She wants to create a table ‘Student’ to store details of her class.

• Which of the following can be the attributes of Student table?

  a) RollNo b) “Amit” c) Name d) 25

• Name the Primary key of the table ‘Student’. State reason for choosing it.

13. Write SQL query to create a table ‘Player’ with the following structure:

14. Anita has created the following table with the name ‘Order’?

One of the rows inserted is as follows :

• What is the data type of columns OrderId and OrderDate in the table Order?
• Anita is now trying to insert the following row:

Will she be able to successfully insert it ? Give reason.

15. Write SQL query to create a table ‘Event’ with the following structure:

+-----------+------------------+-------------+
| Field     |     Type         | Constraint  | 
+-----------+------------------+-------------+
| EventId   |   Varchar(5)     | Primary Key |  
| EventName |   Varchar(30)    | NOT NULL    | 
| Location  |   Varchar(50)    |             | 
| ClientID  |   Integer        |             | 
| EventDate |   Date           |             | 
+-----------+------------------+-------------+

16. Observe the given table carefully and answer the following questions:

• Name the column that might have a Primary Key constraint. Justify your answer.
• Name the column that might have a Unique constraint. Justify your answer.

17. “ABC” Event Management Company requires data of events that are to be organized. Write SQL query to create a table ‘Event’ with the following structure:

18. Suggest her suitable command for the following purpose:

• To display the list of the database already existing in MySQL.
• To use the database named City.
• To remove the pre-existing database named Clients.
• To remove all the records of the table named “Club” at one go along with its structure permanently.

19. While creating a table named “Employee”, Mr. Rishi got confused as which data type he should chose for the column “EName” out of char and varchar. Help him in choosing the right data type to store employee name. Give valid justification for the same?

DDL – Alter Table commands Questions

1. Sahil created a table in Mysql. Later on he found that there should have been another column in the table. Which command should he use to add another column to the table?

2. While creating a table 'Customer' Simrita forgot to set the primary key for the table. Give the statement which she should write now to set the column 'CustiD' as the primary key of the table?

3. Write SQL command to remove column named ‘Hobbies’ from a table named ‘Student’?

4. While creating the table Student last week, Ms. Sharma forgot to include the column Game_Played. Now write a command to insert the Game_Played column with VARCHAR data type and 30 size into the Student table?

5. Rashi wants to add another column ‘Hobbies’ with datatype and size as VARCHAR(50) in the already existing table ‘Student’. She has written the following statement. However it has errors. Rewrite the correct statement?

MODIFY TABLE Student Hobbies VARCHAR;

6. Ms. Shalini has just created a table named “Employee” containing columns Ename, Department, Salary. After creating the table, she realized that she has forgotten to add a primary key column in the table. Help her in writing SQL command to add a primary key column empid. Also state the importance of Primary key in a table.

7. While creating a table 'Customer' Simrita wrongly added a primary key constraint to the field “CUSTNAME”. Now she wants to remove the primary key constraint from the custname field. Help her in writing the correct command?

8. Mr. Akshat have added a not null constraint to the “name” field in “employees” table. But now he wants to remove that not null constraint. Write the command to delete the not null constraint from name field?

9. Kuhu has already created a table ‘Hospital’ as shown below:

Now she wants to add a new column ‘Address’ to the above given table. Suggest suitable MySQL command for the same.

10. Kunal created the following table with the name ‘Friends’ :

+--------------------+------------------+---------------+
| FriendCode         |     Name         | Hobbies       | 
+--------------------+------------------+---------------+
| F101               |   Bijoy          | Swimming      |  
| F102               |   Abhinav        | Reading books | 
| F103               |   Jyotsna        |               | 
+--------------------+------------------+---------------+

Now, Kunal wants to delete the ‘Hobbies’ column. Write the MySQL statement

DML – INSERT INTO commands questions

1. Rama is not able to change a value in a column to NULL. What constraint did she specify when she created the table?

2. Consider the table RESULT given below.

Write command to insert a new row

6, "Mohan", 500, "English", 73, "Second"

3. Consider the Table SHOPPE given below.

To insert a new row in the table Shoppe

'110', 'Pizza' , 'Papa Jones', 120, "Kolkata", 50.0

4. How is NULL value different from 0 (Zero) value?

5. Consider the following table named "GYM"

Add a new row for a new item in GYM with the details: "G107", "Vibro exerciser” ,21000, “GTCFitness"

6. What is meant by NULL value in MySQL?

7. Rewrite the following SQL statement after correcting error(s). Underline the corrections made.

INSERT IN STUDENT(RNO,MARKS) VALUE (5,78.5);

8. Rewrite the following SQL statement after correcting error(s). Underline the corrections made.

INSERT IN EMP(EMPNO, SALES) VALUE (100, 20078.50);

9. Charvi is inserting “Sharma” in the “LastName” column of the “Emp” table but an error is being displayed. Write the correct SQL statement.

INSERT INTO Emp(‘Sharma’)VALUES(LastName);

10. In today’s digitized world with a need to store data electronically, it is very important to store the data in the databases. SQL is used to interact with the Database Management System.

Classify the following commands according to their type: (DDL/DML)

• INSERT INTO
• ALTER TABLE

11. Is NULL and 0(zero) same? Jusify your answer.

12. Write the full forms of the following:

• DDL
• DML

DML – UPDATE and DELETE commands Questions

1. What is the purpose of DROP TABLE command in SOL? How is it different from DELETE command?

2. In a database there are two tables "Product" as shown below:

Write the command To increase the Price of all the Products by 20.

3. Write the UPDATE command to change “Sharma” to “Singh” in the “LastName” column in the Employee table?

4. What is the use of UPDATE statement in SQL ? How is it different from ALTER statement?

5. Consider the following table named "GYM"

Write command To change the Brandname to "Fit Trend India" of the item, whose ICODE as "G101 ".

6. Write the UPDATE statement in MySQL to increase commission by 100.00 in the ‘‘Commission’’ column in the ‘Emp’ table.

7. Write two examples of DML commands of SQL.

8. In a database there are two tables ‘CD’ and ‘TYPE’ as shown below:

Write SQL statement to change the name of Singer ‘‘Sonvi Kumar’’ to ‘‘Sonvi Mehra’’ in all the places wherever it occurs in CD table.

9. Consider the following table named “GARMENT”.

• Write command To change the colour of garment with code as 116 to “Orange”.
• Write command to increase the price of all XL garments by 10%
• Write command to delete the record with GCode “116”

10. In a Database, there are two tables given below:

Write SQL command to change the JOBID to 104 of the Employee with ID as E4 in the table ‘EMPLOYEE’.

11. In Marks column of ‘Student’ table, for Rollnumber 2, the Class Teacher entered the marks as 45. However there was a totaling error and the student has got her marks increased by 5. Which MySQL command should she use to change the marks in ‘Student’ table.

12. Chhavi has created a table named Orders, she has been asked to increase the value of a column named salesamount by 20. She has written the following query for the same.

Alter table Orders Add salesamount =salesamount+20;

Is it the correct query?Justify.

13. Consider the following table:

Table: PharmaDB

Write commands in SQL to increase the price of “Amlodipine” by 50.

DML – SELECT command Questions

1. Pooja, a students of class XI, created a table "Book". Price is a column of this table. To find the details of books whose prices have not been entered she wrote the following query:

Select * from Book where Price = NULL;

2. The LastName column of a table "Directory" is given below:

Based on this information, find the output of the following queries:

• SELECT lastname FROM Directory WHERE lastname like "_a%";
• SELECT lastname FROM Directory WHERE lastname not like "%a";

3. Consider the table TEACHER given below. Write commands in SQL for (1) to (3) and output for (4)

• To display all information about teachers of PGT category.
• To list the names of female teachers of Hindi department.
• To list names, departments and date of hiring of all the teachers in ascending order of date of joining
• SELECT DISTINCT(category) FROM teacher;

4. The ltem_No and Cost columna of a table "ITEMS" are given below:

Based on this information, find the output of the following queries:

• SELECT COST +100 FROM ITEMS WHERE ITEM_NO > 103;

5. Consider the table Projects given below. Write commands in SOL for i) to iii) and output for iv)

• To display all information about projects of"Medium" ProjSize
• To list the ProjSize of projects whose ProjName ends with LITL.
• To list ID, Name, Size, and Cost of all the projects in descending order of StartDate.
• SELECT DISTINCT ProjSize FROM projects

6. The Mname Column of a table Members is given below:

Based on the information, find the output of the following queries :

• Select Mname from members where mname like "%v" ;
• Select Mname from members where mname like "%e%";

7. Sarthya, a student of class XI, created a table "RESULT". Grade is one of the column of this table. To find the details of students whose Grades have not been entered, he wrote the following MySql query, which did not give the desired result.

SELECT * FROM Result WHERE Grade= "Null";

Help Sarthya to run the query by removing the errors from the query and write the correct Query.

8. Consider the table RESULT given below. Write commands in MySql for (i) to (ii)

• To list the names of those students, who have obtained Division as FIRST in the ascending order of NAME.
• To display a report listing NAME, SUBJECT and Annual stipend received assuming that the stipend column has monthly stipend.

9. Mr. Janak is using a table with following columns : Name , Class , Course_Id, Course_name. He needs to display names of students, who have not been assigned any stream or have been assigned Course_name that ends with "economics". He wrote the following command, which did not give the desired result.

SELECT Name, Class FROM Students WHERE Course name = NULL OR Course name="%economics";

Help Mr. Janak to run the query by removing the error and write the correct query.

10. Consider the Table SHOPPE given below. Write command in MySql for (i) to (ii)

• To display names of the items whose name starts with 'C' in ascending order of Price.
• To display Code, Item name and City of the products whose quantity is less than 100.

11. What is used in the SELECT clause to return all the columns in the table?

12. In MySQL, Sumit and Fauzia are getting the following outputs of ItemCodes for SELECT statements used by them on a table named ITEM.(Both have used the SELECT statements on the same table ITEM).

-- Output

+----------------+
| Sumit’s Output |
+----------------+
|  101           | 
|  102           |
|  101           |
|  105           |
|  101           |
|  107           |
+----------------+

+----------------+
| Fauzia Output |
+----------------+
|  101           | 
|  102           |
|  105           |
|  107           |
+----------------+

Which extra keyword has Fauzia used with SELECT statement to get the above output?

13. Consider the table ‘PERSONS’ given below. Write commands in SQL for (i) to (iv) and write output for (v).

• Display the SurNames, FirstNames and Cities of people residing in Udhamwara city.
• Display the Person Ids (PID), cities and Pincodes of persons in descending order of Pincodes.
• Display the First Names and cities of all the females getting Basic salaries above 40000.
• Display First Names and Basic Salaries of all the persons whose firstnames starts with “G”.
• SELECT Surname FROM Persons Where BasicSalary>=50000;

14. Mr. Tondon is using table EMP with the following columns. ECODE,DEPT,ENAME,SALARY. He wants to display all information of employees (from EMP table) in ascending order of ENAME and within it in ascending order of DEPT. He wrote the following command, which did not show the desired output.

SELECT * FROM EMP ORDER BY NAME DESC,DEPT;

Rewrite the above query to get the desired output.

15. Consider the following table named "GYM" with details about fitness items being sold in the store. Write command of SQL for (i) to (ii).

• To display the names of all the items whose name starts with "A".
• To display ICODEs and INAMEs of all items, whose Brandname is Reliable or Coscore.

16. Consider the following table named 'SBOP" with details of account holders. Write commands of MySql for (i) to (ii) and output for (iii).

• To display Accountno, Name and DateOfopen of account holders having transactions more than 8.
• To display all information of account holders whose transaction value is not mentioned.
• SELECT NAME,BALANCE FROM SBOP WHERE NAME LIKE “%i”;

17. When using the LIKE clause, which wildcard symbol represents any sequence of none, one or more characters?