SQL

边玩边学

回答这些题目来赚取能量，然后钓鱼探索。无需账号。

给老师：面向 SQL（Computer Science，CIE）的即用型课程幻灯片, 复习笔记, 图示——用在你的课堂上，或将该知识点作为学生可实时游玩的互动课堂活动来运行。

课程笔记

What is SQL?

SQL (Structured Query Language) is a programming language used to interact with a DBMS.
SQL allows users to select, order, sum, and count data.

SELECT and FROM

SELECT retrieves data from a database table.
Use `SELECT×` to retrieve all columns from a table.
FROM specifies the table(s) to retrieve data from.
Example: `SELECT name, age FROM users;` retrieves names and ages from the 'users' table.

WHERE Clause

WHERE filters data based on a specified condition.
Example: `SELECT×FROM users WHERE age > 30;` retrieves users older than 30.
Conditions can use comparison operators: `=`, `>`, `<`, `≥`, `≤`, `<>`.

AND and OR Operators

AND combines multiple conditions – all must be true.
Example: `SELECT×FROM users WHERE age > 18 AND city = 'New York';`
OR retrieves data when at least one condition is true.
Example: `SELECT×FROM users WHERE age < 18 OR city = 'New York';`

Wildcards and LIKE

Wildcard `%` matches any sequence of characters.
Use LIKE with wildcards for pattern matching.
Example: `SELECT×FROM users WHERE name LIKE 'J%';` finds names starting with 'J'.
`×` selects all columns; `%` is used only with LIKE.

ORDER BY

ORDER BY sorts results in ascending (ASC) or descending (DESC) order.
Default order is ascending.
Example: `SELECT Forename, Lastname FROM Students ORDER BY Lastname ASC;`
Multiple columns can be specified, e.g., `ORDER BY Lastname, Forename ASC`.

SUM and COUNT

SUM adds up and outputs the sum of a numeric field.
Example: `SELECT SUM(Salary) FROM tbl_people;`
COUNT counts the number of rows that match the criteria.
Example: `SELECT COUNT(×) FROM tbl_people WHERE Salary > 50000;`

Worked Example

Given table `tbl_animals` with fields: Animal, Breeding, Number of Young.
To display animals that are breeding and have exactly 1 young:
`SELECT Animal FROM tbl_animals WHERE Breeding = 'Yes' AND Number of Young = 1;`

Structure of a SQL SELECT query showing the order of clauses.

幻灯片

Step through every slide for this topic — plus flashcards and revision notes — with a free account.

练习题

免费预览——53 题中的 8 题。注册即可查看全部。

1.What does SQL stand for?
Easy
- AStructured Query Language
- BSimple Query Language
- CStandard Query Language
- DSequential Query Language
2.Which SQL command is used to retrieve data from a database table?
Easy
- ASELECT
- BFROM
- CWHERE
- DGET
3.In SQL, which keyword is used to filter records based on a condition?
Easy
- AWHERE
- BIF
- CFILTER
- DHAVING
4.What does the '×' symbol represent in SQL?
Easy
- AAll columns
- BAll rows
- CA wildcard character
- DMultiplication
5.Which SQL statement retrieves the name and age of all customers from the 'Customers' table?
Easy
- ASELECT name, age FROM Customers;
- BSELECT name AND age FROM Customers;
- CSELECT×FROM Customers;
- DGET name, age FROM Customers;
6.What is the purpose of the ORDER BY clause in SQL?
Easy
- ATo sort the result set
- BTo filter records
- CTo group records
- DTo count records
7.Which SQL function is used to calculate the total of a numeric column?
Easy
- ASUM
- BCOUNT
- CTOTAL
- DADD
8.Given a table 'Students' with columns 'Name' and 'Age', which SQL statement selects students older than 18?
Easy
- ASELECT×FROM Students WHERE Age > 18;
- BSELECT×FROM Students IF Age > 18;
- CSELECT×FROM Students HAVING Age > 18;

更多题目

9.What does the COUNT function do in SQL?
Easy
- ACounts the number of rows that match a condition
- BCounts the number of columns in a table
- CCounts the sum of a numeric column
- DCounts the number of distinct values
10.Which SQL statement would retrieve all records from the 'Orders' table where the 'Status' is either 'Pending' or 'Shipped'?
Medium
- ASELECT×FROM Orders WHERE Status = 'Pending' OR Status = 'Shipped';
- BSELECT×FROM Orders WHERE Status = 'Pending' AND Status = 'Shipped';
- CSELECT×FROM Orders WHERE Status IN ('Pending', 'Shipped');
- DSELECT×FROM Orders WHERE Status LIKE 'Pending%' OR 'Shipped%';
11.Given a table 'Employees' with columns 'Name', 'Department', and 'Salary', which SQL statement lists all employees from the 'Sales' department with a salary greater than 50000?
Medium
- ASELECT×FROM Employees WHERE Department = 'Sales' AND Salary > 50000;
- BSELECT×FROM Employees WHERE Department = 'Sales' OR Salary > 50000;
- CSELECT×FROM Employees WHERE Department = 'Sales' AND Salary < 50000;
12.Which SQL statement correctly uses a wildcard to find customers whose names start with 'J'?
Easy
- ASELECT×FROM Customers WHERE Name LIKE 'J%';
- BSELECT×FROM Customers WHERE Name = 'J%';
- CSELECT×FROM Customers WHERE Name LIKE '%J';
- DSELECT×FROM Customers WHERE Name = 'J×';
13.Given a table 'Products' with columns 'ProductID', 'Name', 'Price', and 'Quantity', which SQL statement calculates the total value of all products in stock?
Medium
- ASELECT SUM(Price×Quantity) FROM Products;
- BSELECT SUM(Price) FROM Products;
- CSELECT COUNT(Price×Quantity) FROM Products;
- DSELECT TOTAL(Price×Quantity) FROM Products;
14.Consider the table 'Orders' with columns 'OrderID', 'CustomerID', 'OrderDate', and 'Amount'. Which SQL statement retrieves the total amount spent by each customer, sorted by total amount in descending order?
Medium
- ASELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID ORDER BY SUM(Amount) DESC;
- BSELECT CustomerID, SUM(Amount) FROM Orders ORDER BY SUM(Amount) DESC;
- CSELECT CustomerID, SUM(Amount) FROM Orders GROUP BY Amount ORDER BY SUM(Amount) DESC;
- DSELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID ORDER BY Amount DESC;
15.Given a table 'Students' with columns 'StudentID', 'Name', 'Subject', and 'Score', which SQL statement finds the average score for each subject?
Medium
- ASELECT Subject, AVG(Score) FROM Students GROUP BY Subject;
- BSELECT Subject, AVG(Score) FROM Students;
- CSELECT Subject, SUM(Score) FROM Students GROUP BY Subject;
- DSELECT Subject, COUNT(Score) FROM Students GROUP BY Subject;
16.A table 'Sales' has columns 'SaleID', 'Product', 'Quantity', and 'Date'. Which SQL statement finds the product with the highest total quantity sold?
Hard
- ASELECT Product, SUM(Quantity) AS TotalQty FROM Sales GROUP BY Product ORDER BY TotalQty DESC LIMIT 1;
- BSELECT Product, SUM(Quantity) FROM Sales GROUP BY Product ORDER BY Quantity DESC LIMIT 1;
- CSELECT Product, SUM(Quantity) FROM Sales ORDER BY SUM(Quantity) DESC LIMIT 1;
- DSELECT Product, MAX(Quantity) FROM Sales GROUP BY Product ORDER BY MAX(Quantity) DESC LIMIT 1;
17.In SQL, which clause is used to combine rows from two or more tables based on a related column?
Easy
- AJOIN
- BMERGE
- CCOMBINE
- DLINK
18.Given a table 'Employees' with columns 'EmployeeID', 'Name', 'Department', and 'Salary', which SQL statement lists departments with an average salary greater than 50000?
Medium
- ASELECT Department, AVG(Salary) FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000;
- BSELECT Department, AVG(Salary) FROM Employees WHERE AVG(Salary) > 50000 GROUP BY Department;
- CSELECT Department, AVG(Salary) FROM Employees GROUP BY Department WHERE AVG(Salary) > 50000;
- DSELECT Department, AVG(Salary) FROM Employees HAVING AVG(Salary) > 50000;
19.A table 'Books' has columns 'BookID', 'Title', 'Author', and 'YearPublished'. Which SQL statement counts the number of books published after the year 2000?
Medium
- ASELECT COUNT(×) FROM Books WHERE YearPublished > 2000;
- BSELECT COUNT(YearPublished) FROM Books WHERE YearPublished > 2000;
- CSELECT SUM(×) FROM Books WHERE YearPublished > 2000;
- DSELECT COUNT(×) FROM Books HAVING YearPublished > 2000;
20.Given two tables: 'Customers' (CustomerID, Name, City) and 'Orders' (OrderID, CustomerID, Amount). Which SQL statement retrieves the names of customers who have placed at least one order?
Hard
- ASELECT Name FROM Customers WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);
- BSELECT Name FROM Customers WHERE CustomerID = ANY (SELECT CustomerID FROM Orders);
- CSELECT Name FROM Customers WHERE CustomerID EXISTS (SELECT CustomerID FROM Orders);
- DSELECT Name FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders);
21.In SQL, which keyword is used to remove duplicate rows from the result set?
Easy
- ADISTINCT
- BUNIQUE
- CDIFFERENT
- DREMOVE
22.Given a table 'Inventory' with columns 'ItemID', 'ItemName', 'Category', and 'Stock', which SQL statement finds the total stock for each category, but only for categories where total stock is less than 100?
Medium
- ASELECT Category, SUM(Stock) FROM Inventory GROUP BY Category HAVING SUM(Stock) < 100;
- BSELECT Category, SUM(Stock) FROM Inventory WHERE SUM(Stock) < 100 GROUP BY Category;
- CSELECT Category, SUM(Stock) FROM Inventory GROUP BY Category WHERE SUM(Stock) < 100;
- DSELECT Category, SUM(Stock) FROM Inventory HAVING SUM(Stock) < 100;
23.A database has two tables: 'Students' (StudentID, Name) and 'Enrollments' (EnrollmentID, StudentID, CourseID). Which SQL statement finds the names of students who are not enrolled in any course?
Hard
- ASELECT Name FROM Students WHERE StudentID NOT IN (SELECT DISTINCT StudentID FROM Enrollments);
- BSELECT Name FROM Students WHERE StudentID NOT EXISTS (SELECT StudentID FROM Enrollments);
- CSELECT Name FROM Students WHERE StudentID <> ALL (SELECT StudentID FROM Enrollments);
- DSELECT Name FROM Students MINUS SELECT Name FROM Enrollments;
24.Given a table 'Transactions' with columns 'TransactionID', 'AccountID', 'Type' (either 'Deposit' or 'Withdrawal'), and 'Amount'. Which SQL statement calculates the net balance change for each account?
Hard
- ASELECT AccountID, SUM(CASE WHEN Type='Deposit' THEN Amount ELSE -Amount END) FROM Transactions GROUP BY AccountID;
- BSELECT AccountID, SUM(Amount) FROM Transactions GROUP BY AccountID;
- CSELECT AccountID, SUM(Amount) FROM Transactions WHERE Type='Deposit' GROUP BY AccountID;
- DSELECT AccountID, SUM(Amount) FROM Transactions WHERE Type='Withdrawal' GROUP BY AccountID;
25.Which SQL statement would retrieve all records from the 'Students' table where the 'Grade' is 'A'?
Easy
- ASELECT×FROM Students WHERE Grade = 'A';
- BSELECT×FROM Students IF Grade = 'A';
- CSELECT×FROM Students HAVING Grade = 'A';
- DSELECT×FROM Students WHEN Grade = 'A';
26.What does the '%' symbol represent when used with the LIKE operator in SQL?
Easy
- AA wildcard that matches any sequence of characters
- BA wildcard that matches a single character
- CA placeholder for a numeric value
- DA symbol to indicate a comment
27.Given a table 'Products' with columns 'ProductID', 'Name', 'Price', and 'Category', which SQL statement finds the average price of products in the 'Electronics' category?
Medium
- ASELECT AVG(Price) FROM Products WHERE Category = 'Electronics';
- BSELECT AVERAGE(Price) FROM Products WHERE Category = 'Electronics';
- CSELECT SUM(Price) / COUNT(Price) FROM Products HAVING Category = 'Electronics';
- DSELECT AVG(Price) FROM Products HAVING Category = 'Electronics';
28.Consider a table 'Orders' with columns 'OrderID', 'CustomerID', 'OrderDate', and 'Amount'. Which SQL statement retrieves the total amount spent by each customer, sorted by total in descending order?
Medium
- ASELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID ORDER BY SUM(Amount) DESC;
- BSELECT CustomerID, SUM(Amount) FROM Orders ORDER BY SUM(Amount) DESC GROUP BY CustomerID;
- CSELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID ORDER BY Amount DESC;
- DSELECT CustomerID, SUM(Amount) FROM Orders ORDER BY Amount DESC;
29.Given two tables: 'Students' (StudentID, Name, Class) and 'Enrollments' (EnrollmentID, StudentID, CourseID, Grade). Which SQL statement finds the names of students who have a grade of 'A' in any course?
Hard
- ASELECT Name FROM Students WHERE StudentID IN (SELECT StudentID FROM Enrollments WHERE Grade = 'A');
- BSELECT Name FROM Students, Enrollments WHERE Students.StudentID = Enrollments.StudentID AND Grade = 'A';
- CSELECT Name FROM Students INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID WHERE Grade = 'A';
- DAll of the above
30.A table 'Transactions' has columns 'TransactionID', 'AccountID', 'Type' ('Deposit' or 'Withdrawal'), and 'Amount'. Which SQL statement calculates the current balance for each account (assuming starting balance 0)?
Hard
- ASELECT AccountID, SUM(CASE WHEN Type = 'Deposit' THEN Amount WHEN Type = 'Withdrawal' THEN -Amount END) AS Balance FROM Transactions GROUP BY AccountID;
- BSELECT AccountID, SUM(Amount) AS Balance FROM Transactions GROUP BY AccountID;
- CSELECT AccountID, SUM(Amount) FROM Transactions WHERE Type = 'Deposit' GROUP BY AccountID;
- DSELECT AccountID, SUM(Amount) - SUM(Amount) AS Balance FROM Transactions GROUP BY AccountID;
31.Which SQL clause is used to sort the result set?
Easy
- AORDER BY
- BSORT BY
- CGROUP BY
- DARRANGE BY
32.Given a table 'Employees' with columns 'EmployeeID', 'Name', 'Department', and 'Salary', which SQL statement lists departments where the average salary is greater than 50000?
Medium
- ASELECT Department, AVG(Salary) FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000;
- BSELECT Department, AVG(Salary) FROM Employees WHERE AVG(Salary) > 50000 GROUP BY Department;
- CSELECT Department, AVG(Salary) FROM Employees GROUP BY Department WHERE AVG(Salary) > 50000;
- DSELECT Department, AVG(Salary) FROM Employees HAVING AVG(Salary) > 50000;
33.Given a table 'Inventory' with columns 'ItemID', 'ItemName', 'Category', and 'Stock', which SQL statement finds the total stock for each category, but only includes categories with total stock greater than 100?
Medium
- ASELECT Category, SUM(Stock) FROM Inventory GROUP BY Category HAVING SUM(Stock) > 100;
- BSELECT Category, SUM(Stock) FROM Inventory WHERE SUM(Stock) > 100 GROUP BY Category;
- CSELECT Category, SUM(Stock) FROM Inventory GROUP BY Category WHERE SUM(Stock) > 100;
- DSELECT Category, SUM(Stock) FROM Inventory HAVING SUM(Stock) > 100;
34.A database has two tables: 'Customers' (CustomerID, Name, City) and 'Orders' (OrderID, CustomerID, Amount). Which SQL statement retrieves the names of customers who have placed at least one order with amount greater than 100?
Hard
- ASELECT Name FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE Amount > 100);
- BSELECT Name FROM Customers WHERE EXISTS (SELECT×FROM Orders WHERE Orders.CustomerID = Customers.CustomerID AND Amount > 100);
- CSELECT Name FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Amount > 100;
- DAll of the above
35.Given a table 'Orders' with columns 'OrderID', 'CustomerID', 'OrderDate', and 'Amount', which SQL statement retrieves the total amount spent by each customer?
Medium
- ASELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID;
- BSELECT CustomerID, SUM(Amount) FROM Orders;
- CSELECT CustomerID, TOTAL(Amount) FROM Orders GROUP BY CustomerID;
- DSELECT CustomerID, SUM(Amount) FROM Orders ORDER BY CustomerID;
36.Given the table 'Products' with columns 'ProductID', 'Name', 'Price', and 'Quantity', which SQL statement calculates the total value of all products in stock?
Medium
- ASELECT SUM(Price×Quantity) FROM Products;
- BSELECT SUM(Price)×SUM(Quantity) FROM Products;
- CSELECT Price×Quantity FROM Products;
- DSELECT COUNT(Price×Quantity) FROM Products;
37.Consider the table 'Orders' with columns 'OrderID', 'CustomerID', 'OrderDate', and 'Amount'. Which SQL statement retrieves the total amount spent by each customer, only including customers who have spent more than $500 in total?
Medium
- ASELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID HAVING SUM(Amount) > 500;
- BSELECT CustomerID, SUM(Amount) FROM Orders WHERE SUM(Amount) > 500 GROUP BY CustomerID;
- CSELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID WHERE SUM(Amount) > 500;
- DSELECT CustomerID, SUM(Amount) FROM Orders HAVING SUM(Amount) > 500;
38.Given the table 'Students' with columns 'StudentID', 'Name', 'Subject', and 'Score', which SQL statement finds the average score for each subject?
Medium
- ASELECT Subject, AVG(Score) FROM Students GROUP BY Subject;
- BSELECT Subject, AVG(Score) FROM Students;
- CSELECT AVG(Score) FROM Students GROUP BY Subject;
- DSELECT Subject, AVG(Score) FROM Students ORDER BY Subject;
39.Given a table 'Employees' with columns 'EmployeeID', 'Name', 'Department', and 'Salary', which SQL statement lists departments with an average salary greater than $50,000?
Medium
- ASELECT Department, AVG(Salary) FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000;
- BSELECT Department, AVG(Salary) FROM Employees WHERE AVG(Salary) > 50000 GROUP BY Department;
- CSELECT Department, AVG(Salary) FROM Employees GROUP BY Department WHERE AVG(Salary) > 50000;
- DSELECT Department, AVG(Salary) FROM Employees HAVING AVG(Salary) > 50000;
40.Given a table 'Inventory' with columns 'ItemID', 'ItemName', 'Category', and 'Stock', which SQL statement finds the total stock for each category, but only including categories that have more than 10 items in total?
Medium
- ASELECT Category, SUM(Stock) FROM Inventory GROUP BY Category HAVING SUM(Stock) > 10;
- BSELECT Category, SUM(Stock) FROM Inventory WHERE SUM(Stock) > 10 GROUP BY Category;
- CSELECT Category, SUM(Stock) FROM Inventory GROUP BY Category WHERE SUM(Stock) > 10;
- DSELECT Category, SUM(Stock) FROM Inventory HAVING SUM(Stock) > 10;
41.Which SQL clause is used to filter records based on a condition?
Easy
- AWHERE
- BHAVING
- CFILTER
- DCONDITION
42.The '%' symbol in SQL is used as a wildcard character with the LIKE operator.
Easy
True or false?
43.Which SQL function is used to count the number of rows that match a condition?
Easy
- ACOUNT
- BSUM
- CTOTAL
- DNUMBER
44.Given the table 'Students', which SQL statement selects students older than 18?
Easy
- ASELECT×FROM Students WHERE Age > 18;
- BSELECT×FROM Students WHERE Age ≥ 18;
- CSELECT×FROM Students HAVING Age > 18;
- DSELECT×FROM Students FILTER Age > 18;
45.The ORDER BY clause is used to sort the result set in ascending or descending order.
Easy
True or false?
46.Which SQL statement retrieves the total amount spent by each customer from an 'Orders' table?
Medium
- ASELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID;
- BSELECT CustomerID, SUM(Amount) FROM Orders;
- CSELECT CustomerID, COUNT(Amount) FROM Orders GROUP BY CustomerID;
- DSELECT CustomerID, SUM(Amount) FROM Orders ORDER BY CustomerID;
47.Arrange the following SQL clauses in the correct order as they appear in a typical SELECT statement.
Easy
- ORDER BY
- WHERE
- FROM
- SELECT
48.Match each SQL command to its correct description.
Medium
- SELECT
- FROM
- WHERE
- ORDER BY
- Filters records based on a condition
- Specifies which table to retrieve data from
- Retrieves data from a database table
- Sorts the result set
49.Which of the following SQL statements are syntactically correct? (Select all that apply)
Medium
- ASELECT×FROM Students WHERE Age > 18;
- BSELECT Name, Age FROM Students ORDER BY Age;
- CSELECT Name FROM Students WHERE Age > 18 ORDER BY Name;
- DSELECT×FROM Students ORDER BY Age WHERE Age > 18;
- ESELECT Name Age FROM Students;
50.Which SQL keyword is used to retrieve all columns from a table?
Easy
- AALL
- B×
- C%
- DEVERY
51.Complete the SQL statement to display all animals that are breeding and have exactly one young born this year.
Medium
SELECT Animal FROM tbl____animals WHERE Breeding = ____ AND ____ = 1;
52.Complete the SQL statement to display all animals that are breeding and have exactly one young born this year, using the table tbl_animals with columns Animal, Breeding, Number_of_Young.
Easy
SELECT ____ FROM ____ WHERE ____ = 'Yes' AND ____ = 1;
53.Describe the difference between the WHERE and HAVING clauses in SQL.
Medium

Unlock all 53 questions & more

创建免费账号，即可查看该知识点的每一道题、幻灯片、闪卡和复习笔记。

历年真题

该知识点的历年真题练习即将推出。

即将推出