Key Concepts
- 1What is the difference between WHERE and HAVING in SQL?
- 2What are the SQL aggregate functions and what do they do?
- 3How do you use the LIKE operator in SQL? What do % and _ mean?
Important Formulas & Facts
WHERE filters individual rows BEFORE grouping and cannot use aggregate functions. HAVING filters groups AFTER the GROUP BY clause and CAN use aggregate functions. Example: WHERE Marks > 40 filters rows; HAVING AVG(Marks) > 60 filters groups.
SUM() — total of all values in a column. AVG() — arithmetic mean of values. COUNT() — number of rows. MAX() — highest value. MIN() — lowest value. These functions operate on a set of rows and return a single value.
LIKE searches for a pattern in a column. % matches zero or more characters: 'A%' matches any string starting with A. _ matches exactly one character: '_a' matches any two-character string ending in 'a'. Example: WHERE Name LIKE 'S%' finds all names starting with S.
Must-Know Questions
Q1Which SQL command is used to create a new table in a database?
CREATE TABLE is the SQL DDL command used to create a new table in the database. You specify the table name, column names, and their data types along with any constraints.
Q2What does the following SQL command do? ALTER TABLE Student ADD PhoneNo VARCHAR(10);
The ALTER TABLE ... ADD command adds a new column to an existing table. This command adds a column named PhoneNo of type VARCHAR(10) to the Student table.
Q3Which SQL command permanently removes a table and all its data from the database?
DROP TABLE permanently removes the table structure along with all the data it contains. This action cannot be undone. DELETE removes rows but keeps the table structure, while TRUNCATE removes all rows but keeps the table structure.
Q4Write the SQL command to insert a new record into the Student table with RollNo=5, Name='Priya', and Marks=88.
The correct SQL statement is: INSERT INTO Student (RollNo, Name, Marks) VALUES (5, 'Priya', 88); Alternatively, if providing values for all columns in order: INSERT INTO Student VALUES (5, 'Priya', 88);
Q5What is the purpose of the WHERE clause in an SQL UPDATE statement?
The WHERE clause in an UPDATE statement specifies which rows should be updated. Without WHERE, ALL rows in the table would be updated with the new values, which is usually not the intended result.
Practice SQL
Reinforce what you just revised with practice questions