E. SQL Commands

1. What is the SELECT statement?

You use the SELECT command almost every time you query data with SQL. It allows you to define what data you want your query to return. By using the SELECT command with an asterisk (*) all of the columns in the table airbnb_listings are returned.

1SELECT * FROM airbnb_listings;

Using the following command we can find out all the neighbourhoods we have listings in.

1SELECT neighbourhood_group
2FROM airbnb_listings;

However you will notice there are duplicate results. To overcome this we use the SELECT DISTINCT command which will eliminate duplicates and only return distinct data.

1SELECT DISTINCT neighbourhood_group
2FROM airbnb_listings;

2. What are some common clauses used with SELECT query in SQL?

The SQL SELECT statement is commonly used together with:

• WHERE clause to filter records based on specific conditions

1SELECT name, neighbourhood, price 
2FROM airbnb_listings
3WHERE price > 300;

• ORDER BY clause to sort the records on some fields in ascending (ASC) or descending (DESC) order

1SELECT name, neighbourhood, price FROM airbnb_listings
2WHERE price > 300
3ORDER BY price DESC;

• GROUP BY clause to group records with identical data and can be used with an aggregation function to produce a summarized result from the database

1SELECT COUNT(id), neighbourhood
2FROM airbnb_listings
3WHERE price > 100
4GROUP BY neighbourhood;

• HAVING clause to filter records in combination with the GROUP BY clause (NOTE: HAVING is different to WHERE since WHERE cannot filter aggregated records)

1SELECT COUNT(id), neighbourhood
2FROM airbnb_listings
3WHERE price > 100
4GROUP BY neighbourhood
5HAVING COUNT(id) > 5;

3. What are UNION, MINUS and INTERSECT commands?

Firstly for these commands to execute they must meet the following criteria:

• Each SELECT statement within the class must have the same number of columns
• The columns must have similar data types
• The columns in each SELECT statement should necessarily have the same order

UNION

This command is used to combine and return the result-set retrieved by two or more SELECT statements

1-- Fetches the union of queries
2SELECT name FROM Students  
3UNION
4SELECT name FROM Contacts;
5-- Fetch the union of queries with duplicates
6SELECT name FROM Students   
7UNION ALL
8SELECT name FROM Contacts;

MINUS

This command is used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first.

1--  Fetch names from students
2SELECT name FROM Students   /* */
3MINUS     /* that aren't present in contacts */
4SELECT name FROM Contacts;

INTERSECT

This command is used to combine the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets.

1-- Fetch names from students
2SELECT name FROM Students   
3INTERSECT    /* that are present in contacts as well */
4SELECT name FROM Contacts;

Let's look at an actual interview question to see this in practice.

Question Level: Easy

Instagram has requested that you rank users from highest to lowest based on the number of messages they both send and receive.

In our solution we can join both queries using UNION ALL while renaming the column sender and reciever to the same column user_i.

1WITH cte AS
2    (SELECT id, sender AS user_i
3    FROM messages
4    UNION ALL
5    SELECT id, reciever AS user_i
6    FROM messages)
7	
8SELECT user_i, COUNT(id) AS msg_total 
9FROM cte
10GROUP BY 1
11ORDER BY 2 DESC;

Result

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

The UNION and UNION ALL operators both combine rows from result sets into a single result set. However:

• The UNION operator removes eliminate duplicate rows
• The UNION ALL operator does not.

Thus, the UNION ALL operator query runs faster since it does not remove duplicate rows.

5. What is an Alias in SQL?

Aliases are used in SQL to give a table, or a column in a table, a temporary name. They are often used to make column names more readable and only exist for the duration of that query. An Alias is created using the keyword AS. Let’s imagine we have two tables ‘employee A’ and ‘employee B’ with a common column.

1SELECT A.emp_name AS "Employee"  /* Alias using AS keyword */
2B.emp_name AS "Supervisor"
3FROM employee A, employee B   /* Alias without AS keyword */
4WHERE A.emp_sup = B.emp_id;

As seen from above using AS is not compulsory.

1FROM employee A, employee B   /* Alias without AS keyword */
2WHERE A.emp_sup = B.emp_id;

6. What is a View?

A view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. A view is created with the CREATE VIEW statement.

1CREATE VIEW customer_maria AS
2SELECT id, name, price
3FROM airbnb_listings
4WHERE price < 100;

7. What are the TRUNCATE, DELETE and DROP statements?

• TRUNCATE statements are used to remove all data entries from a table in a database while keeping the table and structure in place.

1TRUNCATE TABLE airbnb_listings;  

• DELETE statements Are used to delete rows from our table. To remove data we can simply use the DELETE command based on conditions specified with the WHERE command.

1DELETE FROM airbnb_listings
2WHERE id = 1001;

• DROP statements are used to delete a database or table. Before running this command you should be aware that it will delete your whole table, including all data, indexes, and more, so make sure you are certain before you run.

1DROP TABLE room_details;

To delete a specific column we can combine the ALTER TABLE command with the DROP command.

1ALTER TABLE airbnb_listings 
2DROP COLUMN listings_reserved;

8. What is the difference between DROP and TRUNCATE statements?

When a DROP statement is used the table is dropped, including all things associated with the table. This includes the relationships defined on the table with other tables, the integrity checks and constraints, access privileges and other grants that the table has. To create and use the table again in its original form, all these relations, checks, constraints, privileges, and relationships need to be redefined.

However, when a TRUNCATE statement is used and the table is truncated, none of the above problems exist and the table retains its original structure.

9. What is the difference between DELETE and TRUNCATE statements?

The TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

The DELETE command deletes only the rows from the table based on the condition given in the WHERE clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

10. What is the importance of the TRUNCATE statement?

By using the TRUNCATE TABLE statement, data is removed efficiently and quickly from the table. This statement can also be used to reset values of the identity columns along with data clean-up.

1TRUNCATE TABLE name_of_table 
2RESTART IDENTITY;

This statement can also remove data from multiple tables all at once by mentioning the table names separated by comma.

1TRUNCATE TABLE 
2   table_1, 
3   table_2,
4   table_3;

11. What is the main disadvantage of deleting data from an existing table using the DROP

DROP TABLE command deletes complete data from the table along with removing the complete table structure too. If we just want to remove the data, but not have to recreate the table use the TRUNCATE command.

12. Differentiate between COMMIT and CHECKPOINT.

The COMMIT action ensures that the data consistency of the transaction is maintained, and it ends the current transaction in the section. COMMIT adds a new record in the log that describes the COMMIT to the memory.

A CHECKPOINT on the other hand is used for writing all changes that were committed to disk up to SCN that will be kept in the control file and datafile headers. It guarantees the consistency of the database.

13. How to create empty tables with the same structure as another table?

This can be achieved by fetching the records of one table and placing them into a new table using the INTO operator while fixing a WHERE clause to be false for all records. SQL will prepare the new table with a duplicate structure to accept the fetched records but since no records get fetched due to the WHERE clause in action, nothing is inserted into the new table.

1SELECT* INTO airbnb_listings_copy
2FROM airbnb_listings WHERE 1 = 2;

14. What is Pattern Matching in SQL?

We can search for patterns within our data using the LIKE command. This is useful when you are not sure what exactly it is you are searching for. This kind of SQL query uses wildcards to match a string pattern, rather than writing the exact word. Suppose you want to find listings with country in the title, the code below will achieve this.

1SELECT name
2FROM airbnb_listings
3WHERE name LIKE ‘%country%’;

Other operators for LIKE:

• %x — will select all values that begin with x
• %x% — will select all values that include x
• x% — will select all values that end with x
• x%y — will select all values that begin with x and end with y
• _x% — will select all values have x as the second character
• x% — will select all values that begin with x and are at least two characters long. You can add additional characters to extend the length requirement, i.e. x___%

15. How will you change the datatype of a column?

By using the ALTER TABLE statement with nullability.

1ALTER TABLE TableName 
2ALTER COLUMN ColumnName NVARCHAR(200) [NULL | NOT NULL]

16. What are Common Table Expressions? (CTE's)

Think of a CTE like a temporary table or result set. A CTE is created with the keyword WITH keyword and then referenced to in a SELECT, INSERT, UPDATE, or DELETE statement.

By using CTE's we avoid having to create and drop tables. With a CTE we can simply reference the temporary results set we created.