SQL interview questions

1. What does SQL stand for?

SQL stands for Structured Query Language. People use SQL to manage relational databases.

Full lesson: Sql intro

2. What does the SELECT statement do?

The SELECT statement reads data from a table. The result is a result set.

SELECT * FROM Websites;

Full lesson: Sql select

3. How do you select only some columns from a table?

List the column names after SELECT. The database returns only those columns.

SELECT name, country FROM Websites;

Full lesson: Sql select

4. Which clause filters rows in a query?

The WHERE clause filters rows. It returns only rows that meet the condition.

SELECT * FROM Websites WHERE country = 'CN';

Full lesson: Sql where

5. How do you combine two conditions in a WHERE clause?

Use AND when both conditions must be true. Use OR when one condition is enough.

SELECT * FROM Websites WHERE country = 'CN' AND id > 2;

Full lesson: Sql and or

6. What does the LIKE operator do?

LIKE searches a column for a pattern. The percent sign matches any number of characters.

SELECT * FROM Websites WHERE name LIKE 'G%';

Full lesson: Sql like

7. What does the IN operator do?

IN checks a column against a list of values. The query returns rows that match any value in the list.

SELECT * FROM Websites WHERE country IN ('CN', 'USA');

Full lesson: Sql in

8. How do you give a table or column a short name in a query?

Use an alias to give a table or column a short name. The alias makes the query easier to read.

SELECT w.name FROM Websites AS w;

Full lesson: Sql alias

9. What does INNER JOIN return?

INNER JOIN returns rows that match in both tables. Rows without a match are dropped.

SELECT * FROM Websites INNER JOIN access_log ON Websites.id = access_log.site_id;

Full lesson: Sql join inner

10. What does LEFT JOIN return?

LEFT JOIN returns all rows from the left table. Missing values from the right table become NULL.

SELECT * FROM Websites LEFT JOIN access_log ON Websites.id = access_log.site_id;

Full lesson: Sql join left

11. What does FULL OUTER JOIN return?

FULL OUTER JOIN returns rows from both tables. It keeps matched rows and unmatched rows.

SELECT * FROM Websites FULL OUTER JOIN access_log ON Websites.id = access_log.site_id;

Full lesson: Sql join full

12. How do you limit the number of rows in a result?

Use LIMIT in MySQL to cap the row count. SQL Server uses SELECT TOP for the same result.

SELECT * FROM Websites LIMIT 3;

Full lesson: Sql top

13. How do you create a new table?

Use CREATE TABLE and list each column with its data type. The database then creates the table.

CREATE TABLE Sites (id INT, name VARCHAR(50));

Full lesson: Sql create table

14. Why do you create an index on a table?

An index helps the database find rows faster. Create indexes only on columns that you search often.

CREATE INDEX idx_name ON Websites (name);

Full lesson: Sql create index

15. What is a common mistake with INNER JOIN?

A missing ON condition causes a cross join. The result then holds every row pair. Always add the ON clause.

SELECT * FROM Websites INNER JOIN access_log ON Websites.id = access_log.site_id;

Full lesson: Sql join inner

16. What is SQL?

SQL is a language for storing and working with data in relational databases. You use it to create tables, add data, and run queries.

Full lesson: Sql intro

17. How do you write a basic SQL statement?

A basic SQL statement starts with a keyword such as SELECT. Then you name the columns and the table. End the statement with a semicolon.

SELECT * FROM Customers;

Full lesson: Sql syntax

18. What does the CREATE TABLE statement do?

The CREATE TABLE statement makes a new table in the database. You list each column name and its data type.

CREATE TABLE Persons (
  PersonID int,
  LastName varchar(255)
);

Full lesson: Sql create table

19. What is a data type in SQL?

A data type tells SQL what kind of value a column holds. Examples are integer, varchar, and date. Each column needs a data type.

Full lesson: Sql datatypes general

20. What does the RIGHT JOIN keyword do?

RIGHT JOIN returns all rows from the right table. It also returns matching rows from the left table. If there is no match, the left side shows NULL.

SELECT * FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

Full lesson: Sql join right

21. How do you copy data from one table into a new table?

Use the SELECT INTO statement to copy data into a new table. MySQL does not support SELECT INTO. In MySQL, use CREATE TABLE AS SELECT instead.

CREATE TABLE NewTable AS
SELECT * FROM OldTable;

Full lesson: Sql select into

22. How do you create an index on a table?

Use the CREATE INDEX statement and give the index a name. Then name the table and the column. Indexes speed up searches on that column.

CREATE INDEX idx_lastname
ON Persons (LastName);

Full lesson: Sql create index

23. How do you remove an index, a table, or a database?

Use the DROP statement to delete an index, a table, or a database. The IF EXISTS clause avoids an error when the object is missing.

DROP TABLE IF EXISTS Shippers;

Full lesson: Sql drop

24. What is a view in SQL?

A view is a saved query that looks like a table. It shows rows and columns from one or more real tables. You can use WHERE and JOIN inside a view.

CREATE VIEW CustomerView AS
SELECT CustomerName, City
FROM Customers
WHERE City = 'London';

Full lesson: Sql view

25. How do you get the current date and time in MySQL?

The NOW() function returns the current date and time. You can use it in a SELECT statement or as a default value.

SELECT NOW();

Full lesson: Func now

26. How do you find the number of days between two dates in MySQL?

Use the DATEDIFF() function with two dates. It returns the number of days from the second date to the first date. Only the date part counts.

SELECT DATEDIFF('2008-11-30', '2008-11-29') AS DiffDate;

Full lesson: Func datediff mysql

27. How do you get only the date part from a date and time value in MySQL?

The DATE() function returns only the date part. It removes the time part from a date or datetime value.

SELECT DATE('2008-11-11 13:23:44');

Full lesson: Func date

28. What is a common mistake with RIGHT JOIN?

A common mistake is to think RIGHT JOIN works like LEFT JOIN. RIGHT JOIN keeps all rows from the right table. If you swap the table order, the result changes.

Full lesson: Sql join right

29. What is a common mistake when you create an index?

A common mistake is to create indexes on many columns. Each index slows down updates because the index must also change. Create indexes only on columns you search often.

Full lesson: Sql create index

30. What is a common mistake with the DATEDIFF() function in MySQL?

A common mistake is to expect a time difference in hours or minutes. MySQL DATEDIFF() returns only the number of days. It ignores the time part of each value.

Full lesson: Func datediff mysql

31. What does the UNION operator do?

The UNION operator combines the results of two or more SELECT statements into one result set. Each SELECT must have the same number of columns. The column data types must be similar.

SELECT name FROM Websites
UNION
SELECT name FROM Clients;

Full lesson: Sql union

32. What is the difference between UNION and UNION ALL?

UNION removes duplicate rows from the result. UNION ALL keeps all rows, including duplicates. Use UNION ALL when you need every row.

SELECT name FROM Websites
UNION ALL
SELECT name FROM Clients;

Full lesson: Sql union

33. How do you add a new column to an existing table?

Use the ALTER TABLE statement with the ADD keyword. Name the column and its data type. The new column is added to the table.

ALTER TABLE Websites
ADD age INT;

Full lesson: Sql alter

34. How do you change the data type of a column in MySQL?

Use the ALTER TABLE statement with the MODIFY COLUMN clause. Name the column and the new data type. SQL Server uses ALTER COLUMN instead.

ALTER TABLE Websites
MODIFY COLUMN age VARCHAR(10);

Full lesson: Sql alter

35. What does the CHECK constraint do?

The CHECK constraint limits the values that a column can hold. You can set it on one column or on a whole table. The database rejects rows that fail the check.

CREATE TABLE Persons (
  P_Id int NOT NULL,
  CHECK (P_Id > 0)
);

Full lesson: Sql check

36. How do you create a view in SQL?

Use the CREATE VIEW statement with a name and a SELECT query. The view shows rows and columns like a real table. The data comes from one or more real tables.

CREATE VIEW usa_sites AS
SELECT name, url FROM Websites
WHERE country = 'USA';

Full lesson: Sql view

37. What is a common mistake when you use UNION?

A common mistake is to select a different number of columns in each SELECT. UNION fails when the column counts do not match. Check that each SELECT returns the same number of columns.

SELECT id, name FROM Websites
UNION
SELECT id FROM Clients;

Full lesson: Sql union

38. What is a common mistake with the ALTER TABLE statement?

A common mistake is to drop a column that other objects still need. Some database systems do not allow you to drop a column this way. Check your database rules before you run the statement.

ALTER TABLE Websites
DROP COLUMN age;

Full lesson: Sql alter

39. What is a common mistake when you use a view?

A common mistake is to forget that a view depends on real tables. If you drop a base table, the view stops working. Check the base tables before you change them.

DROP VIEW usa_sites;

Full lesson: Sql view

40. What is a common mistake with the CHECK constraint?

A common mistake is to write a check that the data cannot pass. The database then rejects every new row. Test the condition with real values first.

CREATE TABLE Persons (
  Age int,
  CHECK (Age > 0)
);

Full lesson: Sql check