SQL in DS

DB Schema

It represent how the data is organised & provides information about the relationships between the tables in a given database.

Table Schema: Represent metadata of a table.
1. Attributes (columns) of table
2. Data type of attributes:
i) Numeric (like student_id, age, salary, weight, height, etc.)
ii) String Char/Var char (Name)
iii) Date (Date/timestamp)

Char is fixed length string and var char is a variable length string.

Primary Key :

A column that can be used to uniquely identify each row in the table.

Constraint: Unique + Not Null

Foreign Key:

A column in a table that refers to the primary key in another table. Foreign key link together tables in a relational database.

In reality, 95% of time, there are one to many relationship across the tables.

Fetching substring from a given column

i/p : Elon Musk
o/p : Elon

SELECT
  SUBSTRING_INDEX(full_name," ", 1)

Converting text into date format

SELECT
  DATE_FORMAT(DATE, "%d-%m-%Y")

#OR

SELECT
  DATE_FORMAT("31-01-21", "%d-%m-%Y")

UNION OPERATOR RULES:

It is used to combine 2 SQL queries. Duplicate records will not be returned. If you want to return duplicate records as well use UNION ALL clause.

Rules to use UNION clause:

1. Both queries must return same no. of columns.

2. Columns must be in same order.

3. Datatype of all columns in both the queries must be same.

Constraints

It is used to specify limit or to clarify rule on the data type of the table. It can be specific while creating or altering the table statement.

Window Functions:

Also called analytical function enable to look at different levels of aggregation in the same result row. These function are different from group by clause.

Use of Window Function:

1. Comparison of rows values with aggreagtes

2. Order and rank values

3. Cumulative sums

4. Running sums

5. Comparison with next_value (lead), previous_value (lag), first_value and last_value etc

Joins

It is used to combine rows from one or more tables based on the same column from both tables.

Lets consider an example to understand all the joins.

There are two tables as shown below

Output for left join

Difference between Null and Zero values

For Example: