DEV Community

Sanjeevi Subramani
Sanjeevi Subramani

Posted on • Originally published at lkgforit.com on

SQL Interview Notes

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.

SQL is the standard language for Relational Database System.

DDL Data Definition Language

Command & Description

  1. CREATE

Creates a new table, a view of a table, or other object in the database.

  1. ALTER

Modifies an existing database object, such as a table.

  1. DROP

Deletes an entire table, a view of a table or other objects in the database.

DML Data Manipulation Language

Command & Description

  1. SELECT

Retrieves certain records from one or more tables.

  1. INSERT

Creates a record.

  1. UPDATE

Modifies records.

  1. DELETE

Deletes records.

DCL Data Control Language

Command & Description

  1. GRANT

Gives a privilege to user.

  1. REVOKE

Takes back privileges granted from user.

a. Constraints:

Following are some of the most commonly used constraints available in SQL

NOT NULL Constraint Ensures that a column cannot have a NULL value.

DEFAULT Constraint Provides a default value for a column when none is specified.

UNIQUE Constraint Ensures that all the values in a column are different.

PRIMARY Key Uniquely identifies each row/record in a database table.

FOREIGN Key Uniquely identifies a row/record in any another database table.

CHECK Constraint The CHECK constraint ensures that all values in a column satisfy certain conditions.

INDEX Used to create and retrieve data from the database very quickly.

b. SYNTAX:

SQL SELECT Statement

SELECT column1, column2....columnN

FROM table_name;

Enter fullscreen mode Exit fullscreen mode

SQL DISTINCT Clause

SELECT DISTINCT column1, column2....columnN

FROM table_name;

Enter fullscreen mode Exit fullscreen mode

SQL WHERE Clause

SELECT column1, column2....columnN

FROM table_name

WHERE CONDITION;

Enter fullscreen mode Exit fullscreen mode

SQL AND/OR Clause

SELECT column1, column2....columnN

FROM table_name

WHERE CONDITION-1 {AND|OR} CONDITION-2;

Enter fullscreen mode Exit fullscreen mode

SQL IN Clause

SELECT column1, column2....columnN

FROM table_name

WHERE column_name IN (val-1, val-2,...val-N);

Enter fullscreen mode Exit fullscreen mode

SQL BETWEEN Clause

SELECT column1, column2....columnN

FROM table_name

WHERE column_name BETWEEN val-1 AND val-2;

Enter fullscreen mode Exit fullscreen mode

SQL LIKE Clause

SELECT column1, column2....columnN

FROM table_name

WHERE column_name LIKE { PATTERN };

Enter fullscreen mode Exit fullscreen mode

SQL ORDER BY Clause

SELECT column1, column2....columnN

FROM table_name

WHERE CONDITION

ORDER BY column_name {ASC|DESC};

Enter fullscreen mode Exit fullscreen mode

SQL GROUP BY Clause

SELECT SUM(column_name)

FROM table_name

WHERE CONDITION

GROUP BY column_name;

Enter fullscreen mode Exit fullscreen mode

SQL COUNT Clause

SELECT COUNT(column_name)

FROM table_name

WHERE CONDITION;

Enter fullscreen mode Exit fullscreen mode

SQL HAVING Clause

SELECT SUM(column_name)

FROM table_name

WHERE CONDITION

GROUP BY column_name

HAVING (arithematic function condition);

Enter fullscreen mode Exit fullscreen mode

SQL CREATE TABLE Statement

CREATE TABLE table_name(

column1 datatype,

column2 datatype,

column3 datatype,

.....

columnN datatype,

PRIMARY KEY( one or more columns )

);

Enter fullscreen mode Exit fullscreen mode

SQL DROP TABLE Statement

DROP TABLE table_name;

Enter fullscreen mode Exit fullscreen mode

SQL CREATE INDEX Statement

CREATE UNIQUE INDEX index_name

ON table_name ( column1, column2,...columnN);

Enter fullscreen mode Exit fullscreen mode

SQL DROP INDEX Statement

ALTER TABLE table_name

DROP INDEX index_name;

Enter fullscreen mode Exit fullscreen mode

SQL DESC Statement

DESC table_name;

Enter fullscreen mode Exit fullscreen mode

SQL TRUNCATE TABLE Statement

TRUNCATE TABLE table_name;

Enter fullscreen mode Exit fullscreen mode

SQL ALTER TABLE Statement

ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};

Enter fullscreen mode Exit fullscreen mode

SQL ALTER TABLE Statement (Rename)

ALTER TABLE table_name RENAME TO new_table_name;

Enter fullscreen mode Exit fullscreen mode

SQL INSERT INTO Statement

INSERT INTO table_name( column1, column2....columnN)

VALUES ( value1, value2....valueN);

Enter fullscreen mode Exit fullscreen mode

SQL UPDATE Statement

UPDATE table_name

SET column1 = value1, column2 = value2....columnN=valueN

[WHERE CONDITION];

Enter fullscreen mode Exit fullscreen mode

SQL DELETE Statement

DELETE FROM table_name

WHERE {CONDITION};

Enter fullscreen mode Exit fullscreen mode

SQL CREATE DATABASE Statement

CREATE DATABASE database_name;

Enter fullscreen mode Exit fullscreen mode

SQL DROP DATABASE Statement

DROP DATABASE database_name;

Enter fullscreen mode Exit fullscreen mode

SQL USE Statement

USE database_name;

Enter fullscreen mode Exit fullscreen mode

SQL COMMIT Statement

COMMIT;

Enter fullscreen mode Exit fullscreen mode

SQL ROLLBACK Statement

ROLLBACK;

Enter fullscreen mode Exit fullscreen mode

c. SQL Logical Operators

Here is a list of all the logical operators available in SQL.

Operator & Description

  1. ALL

The ALL operator is used to compare a value to all values in another value set.

  1. AND

The AND operator allows the existence of multiple conditions in an SQL statements WHERE clause.

  1. ANY

The ANY operator is used to compare a value to any applicable value in the list as per the condition.

  1. BETWEEN

The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.

  1. EXISTS

The EXISTS operator is used to search for the presence of a row in a specified table that meets a certain criterion.

  1. IN

The IN operator is used to compare a value to a list of literal values that have been specified.

  1. LIKE

The LIKE operator is used to compare a value to similar values using wildcard operators.

  1. NOT

The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.

  1. OR

The OR operator is used to combine multiple conditions in an SQL statements WHERE clause.

  1. IS NULL

The NULL operator is used to compare a value with a NULL value.

  1. UNIQUE

The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

d. Create Table syntax:

The basic syntax of the CREATE TABLE statement is as follows

CREATE TABLE table_name(

column1 datatype,

column2 datatype,

column3 datatype,

..

columnN datatype,

PRIMARY KEY( one or more columns )

);

The SQL INSERT INTO syntax will be as follows

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,valueN);

The basic syntax of the UPDATE query with a WHERE clause is as follows

UPDATE table_name

SET column1 = value1, column2 = value2., columnN = valueN

WHERE [condition];

DELETE FROM table_name

WHERE [condition];

The basic syntax of % and _ is as follows

SELECT FROM table_name

WHERE column LIKE XXXX%

or

SELECT FROM table_name

WHERE column LIKE %XXXX%

or

SELECT FROM table_name

WHERE column LIKE XXXX_

or

SELECT FROM table_name

WHERE column LIKE _XXXX

or

SELECT FROM table_name

WHERE column LIKE XXXX

The basic syntax of the TOP clause with a SELECT statement would be as follows.

SELECT TOP number|percent column_name(s)

FROM table_name

WHERE [condition]

GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

SELECT column1, column2

FROM table_name

WHERE [conditions]

GROUP BY column1, column2

ORDER BY column1, column2

The basic syntax of DISTINCT keyword to eliminate the duplicate records is as follows

SELECT DISTINCT column1, column2,..columnN

FROM table_name

WHERE [condition]

JOINS:

There are different types of joins available in SQL

INNER JOIN returns rows when there is a match in both tables.

LEFT JOIN returns all rows from the left table, even if there are no matches in the right table.

RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table.

FULL JOIN returns rows when there is a match in one of the tables.

SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN returns the Cartesian product of the sets of records from the two or more joined tables.

INDEX:

Single-Column Indexes

A single-column index is created based on only one table column. The basic syntax is as follows.

CREATE INDEX index_name

ON table_name (column_name);

Enter fullscreen mode Exit fullscreen mode

Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows.

CREATE UNIQUE INDEX index_name

on table_name (column_name);

Enter fullscreen mode Exit fullscreen mode

Composite Indexes

A composite index is an index on two or more columns of a table. Its basic syntax is as follows.

CREATE INDEX index_name

on table_name (column1, column2);

Enter fullscreen mode Exit fullscreen mode

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

RANK:

We have the following rank functions.

  • ROW_NUMBER() -ROW_Number() SQL RANK function to get a unique sequential number for each row

  • RANK()

  • DENSE_RANK() if we have duplicate values, SQL assigns different ranks to those rows as well. Ideally, we should get the same rank for duplicate or similar values.

  • NTILE()

Using SQL Server RANK() function over a result set example

SELECT

product_id,

product_name,

list_price,

RANK () OVER (

ORDER BY list_price DESC

) price_rank

FROM

production.products;

Here is the result set:

Discussion (0)