Questions and Answers
What is the purpose of using SUM(DISTINCT ...) in the SQL query?
To filter out duplicate records
Which of the following SQL queries is correct to count the number of distinct male and female students?
Option B: using COUNT(DISTINCT ...)
How do you express a condition in SQL to check if a price is between 30 and 40?
Using BETWEEN 30 AND 40
What is the purpose of the CASE statement in the SQL query?
Signup and view all the answers
Which of the following SQL queries is correct to update the shelf number of tool books by subtracting 2?
Signup and view all the answers
What is the correct syntax for updating a column value in SQL?
Signup and view all the answers
What is the incorrect statement?
Signup and view all the answers
What is the correct SQL statement to query students who have taken more than 3 courses?
Signup and view all the answers
Which lock mode can be used for data modification operations to ensure that multiple updates are not performed simultaneously on the same resource?
Signup and view all the answers
How many rows are returned by the query that joins the male and female student records by name?
Signup and view all the answers
How many rows are returned by the query that selects students with a non-empty name?
Signup and view all the answers
What statement is used to modify the structure of a table?
Signup and view all the answers
What is the correct SQL statement to query class types with more than 10 students?
Signup and view all the answers
What is the correct SQL statement to query the total number of distinct IDs for male and female students separately?
Signup and view all the answers
Study Notes
SQL Queries and Operations
- In SQL,
*in a query represents all information. - The
avg()function can be used to calculate the average value. - When using the
insertstatement, the data type of the expression does not have to match the data type of the corresponding column in the table.
Querying Data
- To query students who have selected more than 3 courses, the correct SQL statement is:
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) > 3. - To get the number of distinct IDs for male and female students, the correct SQL statement is:
SELECT count(distinct case when sex='男' then id else null end) as man_ids, count(distinct case when sex='女' then id else null end) as women_ids FROM student_table;
Lock Modes
- Exclusive lock (排他锁) can be used to modify data, ensuring that multiple updates are not performed on the same resource simultaneously.
Joining Tables
- To get the number of rows that have the same name for both male and female students, the correct SQL statement is:
SELECT t1.name FROM (SELECT * FROM student_table WHERE sex = '女') t1 INNER JOIN (SELECT * FROM student_table WHERE sex = '男') t2 ON t1.name = t2.name;
Modifying Table Structure
- To modify the structure of a table, the
ALTER TABLEstatement should be used.
Statistical Functions
- To select groups with more than 10 rows, the correct SQL statement is:
SELECT class_type, COUNT(*) FROM STUDENT GROUP BY class_type HAVING COUNT(*) > 10.
Conditional Statements
- To select prices between 30 and 40, the correct SQL statement is:
BETWEEN 30 AND 40.
Updating Data
- To reduce the shelf number by 2 for books of type 'tool', the correct SQL statement is:
UPDATE books SET shelf = shelf - 2 WHERE type = 'tool';
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Description
Test your knowledge of SQL queries, including the use of * and avg() functions, insert statements, and querying data with SELECT, GROUP BY, and HAVING clauses.
