SQL Basics

Acronyms

Tables

Group of records or rows. Created using CREATE TABLE command

Columns

Name of individual content in a record. Type of a column is the type of value it can hold. depends on the DB implementation.

Constraints

Applied to a table using ALTER TABLE command

• NOT NULL - Restricts NULL value from being inserted into a column.
• CHECK - Verifies that all values in a field satisfy a condition.
• DEFAULT - Automatically assigns a default value if no value has been specified for the field.
• UNIQUE - Ensures unique values to be inserted into the field.
• INDEX - Indexes a field providing faster retrieval of records.
• PRIMARY KEY - Uniquely identifies each record in a table.
• FOREIGN KEY - Ensures referential integrity for a record in another table.

Primary Key

PRIMARY KEY constraint uniquely identifies each row in a table

Foreign Key

A FOREIGN KEY consists of a single or collection of fields in a table that essentially refer to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.

Join

The SQL Join clause is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two.

Basically there will be a cross Join and the result will contain columns from both tables. Number of records will be rows(Table-A) * rows(Table-B). Then based on join there will be a filter, like where clause.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Self Join

A self JOIN is a case of regular join where a table is joined to itself based on some relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN clause and a table alias is used to assign different names to the table within the query.

SELECT A.emp_id AS "Emp_ID",A.emp_name AS "Employee",
B.emp_id AS "Sup_ID",B.emp_name AS "Supervisor"
FROM employee A, employee B
WHERE A.emp_sup = B.emp_id;

Cross Join

Cross join can be defined as a cartesian product of the two tables included in the join. The table after join contains the same number of rows as in the cross-product of the number of rows in the two tables. If a WHERE clause is used in cross join then the query will work like an INNER JOIN.

SELECT stu.name, sub.subject
FROM students AS stu
CROSS JOIN subjects AS sub;

Index

A database index is a data structure that provides quick lookup of data in a column or columns of a table. It enhances the speed of operations accessing data from a database table at the cost of additional writes and memory to maintain the index data structure.

Query

A query is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query.

SELECT fname, lname          /* select query */
FROM myDb.students
WHERE student_id = 1;

UPDATE myDB.students          /* action query */
SET fname = 'Captain', lname = 'America'
WHERE student_id = 1;

Sub Query

A subquery is a query within another query, also known as nested query or inner query . It is used to restrict or enhance the data to be queried by the main query, thus restricting or enhancing the output of the main query respectively. For example, here we fetch the contact information for students who have enrolled for the maths subject:

SELECT name, email, mob, address
FROM myDb.contacts
WHERE roll_no IN (
     SELECT roll_no
     FROM myDb.students
     WHERE subject = 'Maths');

Alias

An alias is a feature of SQL that is supported by most, if not all, RDBMSs. It is a temporary name assigned to the table or table column for the purpose of a particular SQL query. In addition, aliasing can be employed as an obfuscation technique to secure the real names of database fields. A table alias is also called a correlation name .

An alias is represented explicitly by the AS keyword but in some cases the same can be performed without it as well. Nevertheless, using the AS keyword is always a good practice.

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

Type of Relations

• One-to-One - This can be defined as the relationship between two tables where each record in one table is associated with the maximum of one record in the other table.
• One-to-Many & Many-to-One - This is the most commonly used relationship where a record in a table is associated with multiple records in the other table.
• Many-to-Many - This is used in cases when multiple instances on both sides are needed for defining a relationship.
• Self Referencing Relationships - This is used when a table needs to define a relationship with itself.

Normalisation

Normalization represents the way of organizing structured data in the database efficiently. It includes creation of tables, establishing relationships between them, and defining rules for those relationships. Inconsistency and redundancy can be kept in check based on these rules, hence, adding flexibility to the database.

First Normal Form

A relation is in first normal form if every attribute in that relation is a single-valued attribute. If a relation contains composite or multi-valued attribute, it violates the first normal form

Second Normal Form

A relation is in second normal form if it satisfies the conditions for first normal form and does not contain any partial dependency. A relation in 2NF has no partial dependency, i.e., it has no non-prime attribute that depends on any proper subset of any candidate key of the table. Often, specifying a single column Primary Key is the solution to the problem

Third Normal Form

A relation is said to be in the third normal form, if it satisfies the conditions for second normal form and there is no transitive dependency between the non-prime attributes, i.e.,all non-prime attributes are determined only by the candidate keys of the relation and not by any other non-prime attribute.

Aggregate and Scalar functions

• AVG() - Calculates the mean of a collection of values.
• COUNT() - Counts the total number of records in a specific table or view.
• etc