All key concepts of SQL for Cracking Interviews and Coding rounds

Basic of SQL

What is database ?

Database is collection of data stored in a format that can easily be accessed, manipulated and managed.

For managing the data we use Database and Management System (DBMS).

There are two types of Database

1. Relational (SQL : Structured Query Language)
2. Non Relational (NoSQL)

Select Statement

For selecting data from table.

Examples :

Select all the data from the customer table

SELECT * FROM customer 

Select name and customer id from customer table

SELECT name,customer_id FROM customer

Select customer name whose id is 1234 from the customer table. (Here using WHERE keyword you can add condition)

SELECT name
FROM customer
WHERE customer_id=1234;

ORDER BY Statement

For sorting the selected records in Ascending order or Descending order.

Examples :

Select name from customer in Ascending order.

SELECT name 
FROM customer 
ORDER BY name

Select name from customer in Descending order.

SELECT name
FROM customer
ORDER BY name DESC

Unique Statement

For selecting unique fields

SELECT DISTINCT column_name FROM table_name

For Selecting Records with Conditions

Example 1

Select customers, whose points are greater than 3000.

SELECT * FROM customers WHERE points > 3000;

Example 2

Select customers who born after 1990–01–01

SELECT * FROM customers WHERE birthdate>'1990-01-01'

AND OR and NOT Statement

Example 1 (AND)

Select customers who born after 1990–01–01 and Having points greater than 1000.

SELECT * FROM customers WHERE birthdate>'1990-01-01' AND points>1000

Example 2 (OR)

Select customers who born after 1990–01–01 or Having points greater than 1000.

SELECT * FROM customers WHERE birthdate>'1990-01-01' OR points>1000;

Example 3 (NOT)

Select customers who don't have points greater than 1000.

SELECT * FROM customers WHERE NOT(points>1000);

IN Operator

Using IN operator we can select fields who have values in given set.

Example

Select states which are ‘VA’ or ‘FL’ or ‘GA’ from customers.

Using Normal Way

SELECT * FROM customers WHERE state='VA' OR state='GA' OR state='FL'

Using IN Operator

SELECT * FROM customers WHERE state IN('VA','FL','GA');
--order doesn't matter  

Between Operator

Using between operator we can get record which are following the specific range.

Example 1

Select customers who have points greater than 1000 and less than 3000.

SELECT * FROM customers WHERE points BETWEEN(1000,3000)

Example 2

Select customer who born between 1990–01–01 and 2022–01–01.

SELECT * FROM customers WHERE birth_date BETWEEN '1990-01-01' AND '2022-01=01'

Like Operator

Like defines similarity. It find all the records which are similar to given field.

Example 1 (With % Operator)

Select all the last name which start with letter ‘b’.

SELECT * FROM customer WHERE last_name LIKE 'b%'

‘%’ sign means any numbers of characters.

Example 2 (With % Operator)

Select all record which contains letter ‘b’.

SELECT * FROM customer WHERE last_name LIKE '%b%'

Example 3 (With ‘_’ Operator)

Select all the customers whose last name is 2 letters long and ends with ‘y’.

SELECT * FROM customers WHERE last_name LIKE '_y'

Example 4 (With ‘_’ Operator)

Select all the customers whose name length is 5, start with ‘b’ and ends with ‘y’.

SELECT * FROM customers WHERE last_name LIKE 'b___y'

‘_’ Underscore represents single character.

REGEXP in SQL

For finding pattern in records.

Example 1

Select customers who contains ‘field’ in last_name.

SELECT * FROM customers WHERE last_name REGEXP 'field'

Example 2 (^ operator)

Select customer who contains ‘field’ in the beginning of last_name.

SELECT * FROM customers WHERE last_name REGEXP '^field'

Example 3 ($ operator)

Select customer who contains ‘field’ in the end of last_name.

SELECT * FROM customers WHERE last_name REGEXP 'field$'

Example 4 (OR , END operator)

Select customers whose last name start with ‘field’ or contains ‘mac’ or ends with ‘rose’. U can use END operator as well.

SELECT * FROM customers WHERE last_name REGEXP '^field | mac |rose$'

Example 5 (Range of chars)

Select customers whose last name contains char from a to h (one char) before ‘e’. ie. range of characters.

SELECT * FROM customers WHERE last_name REGEXP '[a-h]e'

NOTE :

^ => For Beginning

$ => For End

| => For OR

[abcd] => For many chars

[a-f] => For Range of Char

IS NULL Operator

For checking null values in the columns.

Example 1

Find all the records whose phone are NULL.

SELECT * FROM customers WHERE phone IS NULL

Example 2

Find all the records whose phone are NOT NULL.

SELECT * FROM customers WHERE phone IS NOT NULL

ORDER BY in SQL

User to sort records.

Example 1 (For Ascending Order)

Select customers in sorted order by name.

Select * FROM customers ORDER BY name; 

Example 2(For Descending Order)

Select customers in sorted order by name in Descending order.

Select * FROM customers ORDER BY name DESC;

LIMIT Clause in SQL

Limit is used for Limiting records. Means when we write query but we need only some line of its output, at that time LIMIT clause comes into picture.

Example 1

Select only first 3 records from the customer table.

SELECT * FROM customer LIMIT 3;

Example 2 (LIMIT with OFFSET)

Select 3 records after skipping 6 records.

SELECT * FROM customer LIMIT 6,3;

Here 6 is called offset, mean you can skip that number of records.

I am Daily Updating this blog and adding more content to this blog. Please Follow Yashraj Dudhatra For More.