A. SQL Basics

1. What is SQL?

Standard Query Language or SQL is the standard language used for accessing and manipulating databases, especially relational databases where data is organized into rows and columns.

2. What is the difference between SQL and MySQL?

SQL is a standard language for retrieving and manipulating structured databases. MySQL on the other hand is a relational database management system, like SQL Server, Oracle, or IBM DB2, that is used to manage SQL databases.

3. What is a Query?

To put it simply, a request for data from a database. There are two types of database queries; select query and action query.

1-- Select Query
2SELECT id, neighbourhood
3FROM airbnb_listings
4WHERE price < 150;
5
6-- Action Query
7UPDATE airbnb_listings
8SET price = 200
9WHERE id = 2595;

5. What is a Sub-query?

A query placed inside of another query, it can also be referred to as a nested query or inner query. It is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved allowing us to target specific rows to preform various operations.

For example using two tables 'contacts' and 'students' we want to retrieve details of students using a sub-query.

1SELECT name, email, dob, address
2FROM myDb.contacts
3WHERE roll_no IN (
4 SELECT roll_no
5 FROM myDb.students
6 WHERE subject = 'Maths');

4. What is the difference between WHERE and HAVING?

Since the WHERE clause comes before theGROUP BY clause, it cannot be used for filtering data in conjunction with aggregate functions, it can only be used to filter data in each row.

The HAVING clause is used to reduce the number of rows after filtering data with the GROUP BY clause. The HAVING clause filters data in each group of data formed by the GROUP BY clause rather than each row. It follows the GROUP BY clause and precedes the ORDER BY clause.

1# ERROR
2SELECT name, COUNT(orders) AS total_orders
3FROM purchases
4WHERE COUNT(orders) > 20
5GROUP BY 1
6ORDER 2 DESC;
7
8# CORRECT
9SELECT name, COUNT(orders) AS total_orders
10FROM purchases
11GROUP BY 1
12HAVING COUNT(orders) > 20
13ORDER 2 DESC;