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
