Lecture Notes Of Class 3: Basic MySQL Commands and Queries

 

Lecture Notes Of Class 3: Basic MySQL Commands and Queries

---

Objective:

• Learn basic MySQL commands for database creation and management.
• Execute simple SELECT queries to retrieve data.

---

Outcome:

• Students will be able to:
• Create databases.
• Create tables within a database.
• Perform basic SELECT queries to retrieve data from tables.

---

Introduction to MySQL

MySQL is a popular open-source relational database management system (RDBMS) used to manage and organize data. It uses Structured Query Language (SQL) to communicate with databases. SQL commands are used to interact with the data in the database, such as retrieving, inserting, updating, and deleting data.

Basic MySQL Commands

1. Connecting to MySQL

Before we start writing queries, we need to connect to the MySQL server. Here’s how to do it using the command-line interface (CLI):

bash

CopyEdit

mysql -u root -p

• -u root: specifies the username (root is the default).
• -p: prompts for the password (if set).

Once connected, you will see a mysql> prompt where you can enter SQL commands.

2. Creating a Database

A database is a collection of data organized in tables. To create a database, use the CREATE DATABASE command.

Syntax:

sql

CopyEdit

CREATE DATABASE database_name;

Example:

sql

CopyEdit

CREATE DATABASE SchoolDB;

• This creates a database named SchoolDB.

You can check the existing databases with the SHOW DATABASES command:

sql

CopyEdit

SHOW DATABASES;

3. Selecting a Database

Before working with a database, we need to select it using the USE command.

Syntax:

sql

CopyEdit

USE database_name;

Example:

sql

CopyEdit

USE SchoolDB;

• This selects the SchoolDB database for further queries.

4. Creating a Table

A table is a collection of rows and columns that stores data. To create a table, we use the CREATE TABLE command.

Syntax:

sql

CopyEdit

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    column3 datatype,

    ...

);

Example:

sql

CopyEdit

CREATE TABLE Students (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100),

    age INT,

    grade VARCHAR(5)

);

• id: a unique identifier for each student.
• name: stores the student's name.
• age: stores the student's age.
• grade: stores the student's grade.

Key points:

• INT is used for integer values.
• VARCHAR(100) is used for variable-length strings (with a maximum length of 100 characters).
• AUTO_INCREMENT automatically assigns a unique value for each new record.
• PRIMARY KEY ensures the id column has unique values for each row.

You can check the structure of the table using the DESCRIBE command:

sql

CopyEdit

DESCRIBE Students;

5. Inserting Data into a Table

To insert data into a table, we use the INSERT INTO command.

Syntax:

sql

CopyEdit

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

Example:

sql

CopyEdit

INSERT INTO Students (name, age, grade)

VALUES ('John Doe', 20, 'A');

• This inserts a new student named John Doe, 20 years old, with grade 'A' into the Students table.

6. Retrieving Data with SELECT Queries

The most common SQL query is SELECT. It is used to retrieve data from the database.

Syntax:

sql

CopyEdit

SELECT column1, column2, column3, ... FROM table_name;

Example:

sql

CopyEdit

SELECT * FROM Students;

• This retrieves all columns (*) from the Students table.

You can also retrieve specific columns:

sql

CopyEdit

SELECT name, age FROM Students;

• This retrieves only the name and age columns from the Students table.

7. Filtering Data with WHERE Clause

You can filter data using the WHERE clause to retrieve specific rows based on conditions.

Syntax:

sql

CopyEdit

SELECT column1, column2 FROM table_name WHERE condition;

Example:

sql

CopyEdit

SELECT * FROM Students WHERE grade = 'A';

• This retrieves all students who have grade 'A'.

You can use various operators in the WHERE clause, such as:

• = for equality.
• > for greater than.
• < for less than.
• BETWEEN for a range of values.
• IN for a list of values.

Example:

sql

CopyEdit

SELECT * FROM Students WHERE age > 18;

• This retrieves students older than 18.

8. Sorting Data with ORDER BY

To sort the results of a query, we use the ORDER BY clause.

Syntax:

sql

CopyEdit

SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC];

Example:

sql

CopyEdit

SELECT * FROM Students ORDER BY age DESC;

• This sorts the students by age in descending order.

ASC sorts in ascending order (default), and DESC sorts in descending order.

9. Updating Data with UPDATE Command

To modify existing data, we use the UPDATE command.

Syntax:

sql

CopyEdit

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

Example:

sql

CopyEdit

UPDATE Students SET grade = 'B' WHERE name = 'John Doe';

• This updates the grade of the student named 'John Doe' to 'B'.

10. Deleting Data with DELETE Command

To delete data from a table, we use the DELETE command.

Syntax:

sql

CopyEdit

DELETE FROM table_name WHERE condition;

Example:

sql

CopyEdit

DELETE FROM Students WHERE name = 'John Doe';

• This deletes the record for the student named 'John Doe' from the Students table.

---

Summary of Basic MySQL Commands

1.   Create Database: CREATE DATABASE database_name;

2.   Select Database: USE database_name;

3.   Create Table: CREATE TABLE table_name (column definitions);

4.   Insert Data: INSERT INTO table_name (columns) VALUES (values);

5.   Select Data: SELECT columns FROM table_name;

6.   Filter Data: SELECT columns FROM table_name WHERE condition;

7.   Sort Data: SELECT columns FROM table_name ORDER BY column_name [ASC|DESC];

8.   Update Data: UPDATE table_name SET column1 = value1 WHERE condition;

9.   Delete Data: DELETE FROM table_name WHERE condition;

---

Practice Exercises

1.   Create a database named LibraryDB and a table named Books with the following columns: id, title, author, price.

2.   Insert at least 5 records into the Books table.

3.   Retrieve all books with a price greater than 100.

4.   Update the price of a book.

5.   Delete a book from the table.

---

By the end of this class, you should be comfortable with creating databases, tables, and performing basic queries to manipulate data in MySQL.

4o mini

You said:

answer this...Practice Exercises Create a database named LibraryDB and a table named Books with the following columns: id, title, author, price. Insert at least 5 records into the Books table. Retrieve all books with a price greater than 100. Update the price of a book. Delete a book from the table.

ChatGPT said:

ChatGPT

Practice Exercise Solutions

Let's go through each of the exercises one by one:

---

1. Create a database named LibraryDB and a table named Books with the following columns: id, title, author, price.

SQL Command:

sql

CopyEdit

CREATE DATABASE LibraryDB;

USE LibraryDB;

 

CREATE TABLE Books (

    id INT AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(255),

    author VARCHAR(255),

    price DECIMAL(10, 2)

);

• Explanation:
• CREATE DATABASE LibraryDB;: Creates a new database named LibraryDB.
• USE LibraryDB;: Switches to the LibraryDB database.
• CREATE TABLE Books: Creates a table named Books with columns id, title, author, and price.
• AUTO_INCREMENT: Automatically generates a unique number for each new row in the id column.
• DECIMAL(10, 2): Stores numbers with up to 10 digits and 2 decimal places (for the price).

---

2. Insert at least 5 records into the Books table.

SQL Command:

sql

CopyEdit

INSERT INTO Books (title, author, price) VALUES

('The Great Gatsby', 'F. Scott Fitzgerald', 120.50),

('To Kill a Mockingbird', 'Harper Lee', 95.00),

('1984', 'George Orwell', 150.00),

('Pride and Prejudice', 'Jane Austen', 80.00),

('The Catcher in the Rye', 'J.D. Salinger', 110.75);

• Explanation:
• We are inserting 5 records into the Books table with the respective title, author, and price.
• The id column will be automatically populated because of the AUTO_INCREMENT.

---

3. Retrieve all books with a price greater than 100.

SQL Command:

sql

CopyEdit

SELECT * FROM Books WHERE price > 100;

• Explanation:
• SELECT *: Retrieves all columns for each row.
• WHERE price > 100: Filters the records to show only those where the price is greater than 100.

Result:

id	title	author	price
1	The Great Gatsby	F. Scott Fitzgerald	120.50
3	1984	George Orwell	150.00
5	The Catcher in the Rye	J.D. Salinger	110.75

---

4. Update the price of a book.

Let’s say we want to update the price of the book 'To Kill a Mockingbird'.

SQL Command:

sql

CopyEdit

UPDATE Books SET price = 105.00 WHERE title = 'To Kill a Mockingbird';

• Explanation:
• UPDATE Books: Specifies the Books table to be updated.
• SET price = 105.00: Changes the price of the book to 105.00.
• WHERE title = 'To Kill a Mockingbird': Ensures that only the row with the book "To Kill a Mockingbird" is updated.

Result: The price of "To Kill a Mockingbird" will now be updated to 105.00.

---

5. Delete a book from the table.

Let’s say we want to delete the book 'Pride and Prejudice'.

SQL Command:

sql

CopyEdit

DELETE FROM Books WHERE title = 'Pride and Prejudice';

• Explanation:
• DELETE FROM Books: Specifies the Books table from which to delete the record.
• WHERE title = 'Pride and Prejudice': Ensures that only the row with the book "Pride and Prejudice" is deleted.

Result: The book "Pride and Prejudice" will be deleted from the Books table.

---

Summary of the Commands

1.   Create Database & Table:

sql

CopyEdit

CREATE DATABASE LibraryDB;

USE LibraryDB;

CREATE TABLE Books (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), author VARCHAR(255), price DECIMAL(10, 2));

2.   Insert Data:

sql

CopyEdit

INSERT INTO Books (title, author, price) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 120.50), ...;

3.   Retrieve Books with Price > 100:

sql

CopyEdit

SELECT * FROM Books WHERE price > 100;

4.   Update Price of a Book:

sql

CopyEdit

UPDATE Books SET price = 105.00 WHERE title = 'To Kill a Mockingbird';

5.   Delete a Book:

sql

CopyEdit

DELETE FROM Books WHERE title = 'Pride and Prejudice';

---

These exercises cover basic MySQL operations, such as creating databases, inserting data, querying data, updating records, and deleting data. Let me know if you need further clarification!

 

 

Assignment 1: Create a Database

Objective: Create a new database named school.

Problem:

Write a MySQL command to create a database named school.

Solution:

sql

CopyEdit

CREATE DATABASE school;

Explanation:

• The CREATE DATABASE statement is used to create a new database.
• The name of the database in this case is school.

Output:

If successful, MySQL will display the message:

graphql

CopyEdit

Query OK, 1 row affected

---

Assignment 2: Create a Table

Objective: Create a table students inside the school database to store student details.

Problem:

Write a MySQL command to create a table students with the following fields:

• id (INT, primary key, auto-increment)
• name (VARCHAR, length 100)
• age (INT)
• grade (VARCHAR, length 2)

Solution:

sql

CopyEdit

USE school;

 

CREATE TABLE students (

  id INT AUTO_INCREMENT PRIMARY KEY,

  name VARCHAR(100),

  age INT,

  grade VARCHAR(2)

);

Explanation:

• USE school switches to the school database.
• CREATE TABLE is used to create a table named students.
• id is an integer column that automatically increments with each new record, and it is also the primary key.
• name stores the student's name as a string of up to 100 characters.
• age stores the student's age as an integer.
• grade stores the student's grade as a string with a maximum length of 2 characters.

Output:

If successful, MySQL will display the message:

graphql

CopyEdit

Query OK, 0 rows affected

---

Assignment 3: Insert Data into Table

Objective: Insert data into the students table.

Problem:

Write a MySQL command to insert the following student details into the students table:

• name: "John Doe"
• age: 16
• grade: "A"

Solution:

sql

CopyEdit

INSERT INTO students (name, age, grade)

VALUES ('John Doe', 16, 'A');

Explanation:

• INSERT INTO is used to add data to the table.
• We specify the column names (name, age, grade) and the values to be inserted.

Output:

If successful, MySQL will display the message:

graphql

CopyEdit

Query OK, 1 row affected

---

Assignment 4: Select All Data from Table

Objective: Retrieve all records from the students table.

Problem:

Write a MySQL command to select all columns and rows from the students table.

Solution:

sql

CopyEdit

SELECT * FROM students;

Explanation:

• SELECT * selects all columns from the students table.
• FROM students specifies the table to query.

Output:

If there is data in the table, the output will look like:

pgsql

CopyEdit

+----+-----------+-----+-------+

| id | name      | age | grade |

+----+-----------+-----+-------+

|  1 | John Doe  |  16 | A     |

+----+-----------+-----+-------+

1 row in set (0.00 sec)

---

Assignment 5: Select Specific Columns

Objective: Retrieve specific columns from the students table.

Problem:

Write a MySQL command to select only the name and age columns from the students table.

Solution:

sql

CopyEdit

SELECT name, age FROM students;

Explanation:

• Instead of using *, we specify the columns (name, age) we want to retrieve.

Output:

pgsql

CopyEdit

+-----------+-----+

| name      | age |

+-----------+-----+

| John Doe  |  16 |

+-----------+-----+

1 row in set (0.00 sec)

---

Assignment 6: Update Data in a Table

Objective: Update the grade of a student.

Problem:

Write a MySQL command to update the grade of the student with id = 1 to B.

Solution:

sql

CopyEdit

UPDATE students

SET grade = 'B'

WHERE id = 1;

Explanation:

• UPDATE modifies existing records in a table.
• SET grade = 'B' changes the grade to B.
• WHERE id = 1 ensures that only the record with id = 1 is updated.

Output:

If successful, MySQL will display the message:

graphql

CopyEdit

Query OK, 1 row affected

---

Assignment 7: Delete Data from a Table

Objective: Delete a student from the students table.

Problem:

Write a MySQL command to delete the student with id = 1 from the students table.

Solution:

sql

CopyEdit

DELETE FROM students

WHERE id = 1;

Explanation:

• DELETE FROM is used to remove records from the table.
• WHERE id = 1 ensures that only the record with id = 1 is deleted.

Output:

If successful, MySQL will display the message:

graphql

CopyEdit

Query OK, 1 row affected

---

Assignment 8: Filter Data with WHERE Clause

Objective: Retrieve students who are 16 years old.

Problem:

Write a MySQL command to select all students who are 16 years old.

Solution:

sql

CopyEdit

SELECT * FROM students

WHERE age = 16;

Explanation:

• The WHERE clause filters the records to return only students where age = 16.

Output:

If there is a student who is 16 years old, the output will look like:

pgsql

CopyEdit

+----+-----------+-----+-------+

| id | name      | age | grade |

+----+-----------+-----+-------+

|  1 | John Doe  |  16 | A     |

+----+-----------+-----+-------+

1 row in set (0.00 sec)

---

Assignment 9: Sort Data with ORDER BY

Objective: Retrieve students sorted by age in descending order.

Problem:

Write a MySQL command to select all students and order them by age in descending order.

Solution:

sql

CopyEdit

SELECT * FROM students

ORDER BY age DESC;

Explanation:

• ORDER BY age DESC sorts the results by the age column in descending order (DESC).

Output:

If there are multiple students, the output will show them sorted by age from the oldest to the youngest.

---

Assignment 10: Count the Number of Students

Objective: Count how many students are in the students table.

Problem:

Write a MySQL command to count the number of students in the table.

Solution:

sql

CopyEdit

SELECT COUNT(*) FROM students;

Explanation:

• COUNT(*) counts all rows in the table.

Output:

If there is one student, the output will look like:

sql

CopyEdit

+----------+

| COUNT(*) |

+----------+

|        1 |

+----------+

1 row in set (0.00 sec)

---

These assignments cover the essential MySQL commands and queries that every student should learn in order to get hands-on experience with database creation, data manipulation, and query execution.

4o mini

You said:

write 50 MCQs for the concept above with explanation

ChatGPT said:

ChatGPT

Here are 50 Multiple Choice Questions (MCQs) on Basic MySQL Commands and Queries with explanations for each:

---

1. Which command is used to create a new database in MySQL?

a) CREATE DATABASE
b) ADD DATABASE
c) NEW DATABASE
d) MAKE DATABASE

Answer: a) CREATE DATABASE
Explanation: The CREATE DATABASE command is used to create a new database in MySQL.

---

2. Which of the following command is used to select all records from a table?

a) SELECT ALL FROM table;
b) SELECT * FROM table;
c) SELECT RECORDS FROM table;
d) SELECT ALL RECORDS FROM table;

Answer: b) SELECT * FROM table;
Explanation: SELECT * retrieves all columns and rows from a table.

---

3. Which command is used to delete a table in MySQL?

a) REMOVE TABLE
b) DROP TABLE
c) DELETE TABLE
d) CLEAR TABLE

Answer: b) DROP TABLE
Explanation: The DROP TABLE command deletes the entire table and its data.

---

4. Which of the following is the correct command to create a table?

a) CREATE TABLE table_name;
b) CREATE TABLE table_name (column1 datatype, column2 datatype);
c) NEW TABLE table_name (column1 datatype, column2 datatype);
d) ADD TABLE table_name (column1 datatype, column2 datatype);

Answer: b) CREATE TABLE table_name (column1 datatype, column2 datatype);
Explanation: The CREATE TABLE command defines the structure of a new table, including columns and their data types.

---

5. How do you add a new record to an existing table?

a) INSERT INTO table_name VALUES (value1, value2);
b) ADD INTO table_name VALUES (value1, value2);
c) CREATE INTO table_name VALUES (value1, value2);
d) NEW INTO table_name VALUES (value1, value2);

Answer: a) INSERT INTO table_name VALUES (value1, value2);
Explanation: The INSERT INTO command is used to add new records to a table.

---

6. Which of the following is used to modify an existing record in a table?

a) CHANGE
b) MODIFY
c) UPDATE
d) ALTER

Answer: c) UPDATE
Explanation: The UPDATE command is used to modify existing data in a table.

---

7. What does the WHERE clause do in a SQL query?

a) Defines the table to retrieve from
b) Specifies which columns to retrieve
c) Filters the records to select only specific rows
d) Orders the results

Answer: c) Filters the records to select only specific rows
Explanation: The WHERE clause is used to filter records based on specified conditions.

---

8. Which command is used to count the number of rows in a table?

a) COUNTROWS
b) TOTAL
c) COUNT(*)
d) ROWCOUNT

Answer: c) COUNT(*)
Explanation: COUNT(*) returns the number of rows in a table.

---

9. Which clause is used to sort the result set in SQL?

a) ORDER BY
b) SORT
c) GROUP BY
d) ORDER

Answer: a) ORDER BY
Explanation: The ORDER BY clause is used to sort the results based on one or more columns.

---

10. Which command is used to remove all rows from a table without deleting the table itself?

a) DELETE FROM table_name;
b) REMOVE FROM table_name;
c) DROP FROM table_name;
d) CLEAR FROM table_name;

Answer: a) DELETE FROM table_name;
Explanation: DELETE FROM removes all rows but keeps the table structure intact.

---

11. Which command will modify the structure of an existing table?

a) UPDATE
b) ALTER
c) MODIFY
d) CHANGE

Answer: b) ALTER
Explanation: The ALTER command is used to modify the structure of an existing table.

---

12. Which keyword is used to sort the result set in descending order?

a) DESCENDING
b) ORDER DESC
c) ORDER BY DESC
d) SORT DESC

Answer: c) ORDER BY DESC
Explanation: The ORDER BY clause with DESC sorts the result set in descending order.

---

13. What will the following query do?

sql

CopyEdit

SELECT * FROM students WHERE age > 18;

a) Select all students with age greater than 18
b) Select all students whose age is 18
c) Select students who are younger than 18
d) Select all students without any condition

Answer: a) Select all students with age greater than 18
Explanation: The WHERE clause filters the records to select students whose age is greater than 18.

---

14. What does AUTO_INCREMENT do in MySQL?

a) Automatically increases the value of a column
b) Increments a column by 1
c) Automatically generates a unique value for a column
d) Sets a limit on the number of rows in a table

Answer: c) Automatically generates a unique value for a column
Explanation: AUTO_INCREMENT is used to automatically generate a unique value for a column, usually used for primary keys.

---

15. Which of the following is used to delete a specific row from a table?

a) DELETE
b) REMOVE
c) DROP
d) TRUNCATE

Answer: a) DELETE
Explanation: The DELETE command removes a specific row from a table based on the condition specified by the WHERE clause.

---

16. Which keyword is used to ensure that no duplicate records are returned in the result set?

a) UNIQUE
b) DISTINCT
c) NO_DUPLICATE
d) EXCLUSIVE

Answer: b) DISTINCT
Explanation: The DISTINCT keyword is used to remove duplicates from the result set.

---

17. Which command would you use to remove a database from MySQL?

a) DELETE DATABASE
b) DROP DATABASE
c) REMOVE DATABASE
d) CLEAR DATABASE

Answer: b) DROP DATABASE
Explanation: DROP DATABASE is used to delete a database and all of its objects.

---

18. Which of the following is true about the GROUP BY clause?

a) It is used to group rows that have the same values in specified columns
b) It is used to join multiple tables
c) It orders the rows in ascending order
d) It is used to select specific columns from a table

Answer: a) It is used to group rows that have the same values in specified columns
Explanation: The GROUP BY clause is used to group rows that share a property, often used with aggregate functions like COUNT().

---

19. Which of the following functions is used to return the highest value in a column?

a) MAX()
b) TOP()
c) HIGHEST()
d) GREATEST()

Answer: a) MAX()
Explanation: The MAX() function is used to return the highest value in a column.

---

20. What does the LIMIT clause do in a SQL query?

a) Limits the number of rows returned by the query
b) Limits the number of columns returned
c) Limits the database connection
d) Limits the number of databases created

Answer: a) Limits the number of rows returned by the query
Explanation: The LIMIT clause is used to specify the maximum number of records to return.

---

21. Which of the following is used to modify the data type of a column?

a) UPDATE COLUMN
b) CHANGE COLUMN
c) ALTER COLUMN
d) MODIFY COLUMN

Answer: b) CHANGE COLUMN
Explanation: The CHANGE COLUMN is used to modify the column name and data type.

---

22. Which function is used to get the average value of a numeric column?

a) AVG()
b) MEAN()
c) AVERAGE()
d) MEDIAN()

Answer: a) AVG()
Explanation: The AVG() function is used to calculate the average value of a numeric column.

---

23. Which clause is used to filter results based on a condition after sorting?

a) WHERE
b) HAVING
c) GROUP BY
d) ORDER BY

Answer: b) HAVING
Explanation: HAVING is used to filter records after the GROUP BY clause.

---

24. Which command is used to rename a column in a table?

a) CHANGE COLUMN
b) MODIFY COLUMN
c) RENAME COLUMN
d) ALTER COLUMN

Answer: a) CHANGE COLUMN
Explanation: The CHANGE COLUMN command allows you to rename an existing column in a table.

---

25. Which of the following is true about the JOIN clause?

a) It is used to combine records from multiple tables
b) It is used to combine two databases
c) It is used to filter rows from a single table
d) It is used to create new tables

Answer: a) It is used to combine records from multiple tables
Explanation: The JOIN clause is used to combine records from two or more tables based on related columns.

---

26. Which command is used to update an existing record in MySQL?

a) MODIFY
b) CHANGE
c) UPDATE
d) ALTER

Answer: c) UPDATE
Explanation: The UPDATE command is used to modify existing records in a table.

---

27. Which of the following commands would you use to view the structure of a table?

a) SHOW TABLE
b) DESCRIBE table_name
c) STRUCTURE table_name
d) VIEW table_name

Answer: b) DESCRIBE table_name
Explanation: DESCRIBE shows the structure of a table, including columns and their data types.

---

28. Which MySQL command is used to return all columns but only unique rows?

a) SELECT DISTINCT
b) SELECT UNIQUE
c) SELECT DIFFERENT
d) SELECT UNIQUE ROWS

Answer: a) SELECT DISTINCT
Explanation: SELECT DISTINCT removes duplicates and returns only unique rows.

---

29. What does the TRUNCATE command do in MySQL?

a) Deletes all rows in a table and resets the auto-increment value
b) Deletes a specific row in a table
c) Deletes a column from a table
d) Removes a table structure

Answer: a) Deletes all rows in a table and resets the auto-increment value
Explanation: TRUNCATE removes all rows from a table and also resets the AUTO_INCREMENT value to its initial value.

---

30. Which of the following is true about the IN operator in SQL?

a) It is used to filter records that match one of several values
b) It is used to filter records that match all values
c) It is used to join multiple tables
d) It is used to update records

Answer: a) It is used to filter records that match one of several values
Explanation: The IN operator allows you to specify multiple values in the WHERE clause.

---

31. Which of the following is used to ensure data integrity in a relational database?

a) INDEX
b) CONSTRAINT
c) TRIGGER
d) FOREIGN KEY

Answer: b) CONSTRAINT
Explanation: Constraints such as PRIMARY KEY, FOREIGN KEY, and UNIQUE are used to ensure data integrity.

---

32. Which clause is used to combine rows from two or more tables based on a related column?

a) UNION
b) COMBINE
c) JOIN
d) INTERSECT

Answer: c) JOIN
Explanation: The JOIN clause combines rows from two or more tables based on a related column.

---

33. Which SQL function is used to round a number to the nearest integer?

a) ROUND()
b) CEILING()
c) FLOOR()
d) TRUNCATE()

Answer: a) ROUND()
Explanation: The ROUND() function is used to round a number to the nearest integer.

---

34. What is the result of the following query?

sql

CopyEdit

SELECT COUNT(*) FROM students WHERE age < 18;

a) Counts the total number of students
b) Counts the number of students under 18 years of age
c) Counts the number of students above 18 years of age
d) Counts the number of students with age exactly 18

Answer: b) Counts the number of students under 18 years of age
Explanation: The query counts the number of students whose age is less than 18.

---

35. Which of the following is used to fetch records from multiple tables in SQL?

a) JOIN
b) SELECT
c) FETCH
d) UNION

Answer: a) JOIN
Explanation: JOIN is used to fetch records from multiple tables based on a related column.

---

36. Which SQL statement will return all unique values from the grade column in the students table?

a) SELECT DISTINCT grade FROM students;
b) SELECT grade FROM students;
c) SELECT UNIQUE grade FROM students;
d) SELECT DIFFERENT grade FROM students;

Answer: a) SELECT DISTINCT grade FROM students;
Explanation: SELECT DISTINCT returns only unique values from the grade column.

---

37. What will be the output of the following query?

sql

CopyEdit

SELECT MAX(age) FROM students;

a) The lowest age
b) The total number of students
c) The average age of students
d) The highest age

Answer: d) The highest age