1

SQL
Build database:
Create table, insert into ,Alter table, Join , update,delete
Query:
single
1.select with where condition
2.order with asec or desc
3.group with have /where condition
Multiple:
Union,union all, minus, intersect
Subquery:
1. What is Database?
database is a storage space.It is used to store and retrieve the data.
2. What is DBMS?
DBMS stands for Database Management System. DBMS is a system software used to
store and retrieve data from database efficiently. It is an interface between the
database and its end users .
There are two types of DBMS:
Relational Database Management System: The data is stored in tables.SQL used to
interact with database. Example – MySQL.
Non-Relational Database Management System: Here data not stored in Tables. NO-
SQL used to interact with database. Example – MongoDB.

What is SQL,MySQL,NOSQL?
SQL means structured query language.it is used for data query,data
manipulation(insert,delete,update) in relational database . The SQL Keywords
are case-insensitive (SELECT, FROM, WHERE, AS, ORDER BY, HAVING, GROUP BY,
etc), but are usually written in all capitals. However, in some settings table and
column names are case-sensitive. MySQL has a configuration option to enable or
disable it.

Mahesh Kumar S
 2

Mysql-It is a open source relational database management system .it uses SQL
language to intract with the database.(written in c and c++,maintained by ORACLE)
Nosql(not only sql)-
SQL/relational databases use structured query language. Each NoSQL/non-relational
database offered its own unique query language.
SQL databases are vertically scalable, NoSQL databases are horizontally scalable.
SQL databases are not best suited for hierarchical data storage. NoSQL databases are
best suited for hierarchical data storage.
SQL databases are table based, while NoSQL databases are document, key-value,
graph based.
Example of NOSQL: mongoDB, CASSENDRA, NEO4J
Types of NoSQL databases :
Key value store: Memcached, Redis, Coherence
Tabular: Hbase, Big Table, Accumulo
Document based: MongoDB, CouchDB, Cloudant
Graph:neo4j
When should NoSQL be used:
When huge amount of data and data are in the form of hierarchical architecture
need to be stored and retrieved
SQL databases are better for multi-row transactions, NoSQL are better for
unstructured data like documents or JSON.
Sql vs plsql:

Mahesh Kumar S
 3

7. What are Constraints in SQL?

Constraints are set of rules used to maintain the integrity in database.

 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.
 PRIMARY KEY - Uniquely identifies each record in a table.
 FOREIGN KEY - Ensures referential integrity for a record in another table.
What is a Primary Key?
The PRIMARY KEY used for uniquely identifies each row in a table. It must contain
UNIQUE values and has NOT NULL constraint.
A table in SQL has one and only one primary key, which is comprised of single or
multiple fields (columns).

Mahesh Kumar S
 4

9. What is a UNIQUE constraint?

A UNIQUE constraint ensures that all values in a column are different. Unlike primary
key, there can be multiple unique constraints defined per table. A column with
unique constraint may contain NULL value.

Mahesh Kumar S
 5

What is the difference between primary key and unique constraints?

Primary key cannot have NULL value, the unique constraints can have NULL values.
There is only one primary key in a table, but there can be multiple unique constrains.
What is a Foreign Key?
A FOREIGN KEY is a column (or collection of fields) in one table that refers to the
PRIMARY KEY in another table .Foreign key constraint ensures referential integrity in
the relation between two tables.

Mahesh Kumar S
 6

Mahesh Kumar S
 7

As we can see clearly that the field C_ID in Orders table is the primary key in
Customers table, i.e. it uniquely identifies each row in the Customers table.
Therefore, it is a Foreign Key in Orders table.
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.

Mahesh Kumar S
 8

Output:

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:
1. INNER Join
2. OUTER Join
(INNER) JOIN: Returns records that have matching particular column 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.NULL value filled if corresponding col has no values.

Mahesh Kumar S
 9

Mahesh Kumar S
 10

What is Cross Join in SQL?

The SQL CROSS JOIN produces a result set which is the number of rows in the first
table multiplied by the number of rows in the second table if no WHERE clause is
used along with CROSS JOIN. This kind of result is called as Cartesian Product.
If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

Mahesh Kumar S
 11

SQL Self JOIN

A self JOIN is a regular join, but the table is joined with itself.

Mahesh Kumar S
 12

17. What is a Query?

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

What is a Subquery? What are its types?

A Subquery or Inner query or a Nested query is a query within another SQL query and
used with WHERE clause. A subquery return data that will be used in the main query

Mahesh Kumar S
 13

In a single query:
Select a,b from t1,t2 where t1.r=t2.r and t2.sub=’maths’
What is the SELECT statement?
SELECT operator in SQL is used to select data from a database

What are some common clauses used with SELECT query in SQL?
Some common SQL clauses used in conjuction with a SELECT query are as follows:
WHERE clause in SQL is used to filter records that are necessary, based on specific
conditions.
ORDER BY clause in SQL is used to sort the records based on some field(s) in
ascending (ASC) or descending order (DESC).

GROUP BY used to cluster the data with respect particular column.and it used with
aggregate function.
HAVING clause in SQL is used to filter records in combination with the GROUP BY
clause. It is different from WHERE, since WHERE clause cannot filter aggregated
records.

Mahesh Kumar S
 14

What are UNION, MINUS and INTERSECT commands?

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT
statements.

The MINUS operator in SQL is used to remove duplicates from the result-set
obtained by the second SELECT query from the result-set obtained by the first SELECT
query and then return the filtered results from the first.
The INTERSECT clause in SQL combines the result-set fetched by the two SELECT
statements where records from one match the other and then returns this
intersection of result-sets.
Certain conditions need to be met before executing either of the above statements
in SQL -
Each SELECT statement within the clause must have the same number of columns
The columns must also have similar data types
The columns in each SELECT statement should necessarily have the same order
UNION ALL Syntax

The UNION operator selects only distinct values by default. To allow duplicate values,
use UNION ALL

Mahesh Kumar S
 15

Mahesh Kumar S
 16

What is index?
Index is a datastructure which is enable fast retrival of data from database.Index
table contain index id and pointer.pointer points to data in database.Each index
name must be unique in the database. They are similar to textbook indexes. In
textbooks, if you need to go to a particular chapter, you go to the index, find the
page number of the chapter and go directly to that page.
The same applies to indexes in databases. Without indexes, a DBMS has to go
through all the records in the table in order to retrieve the desired results. On the
other hand, if you create indexes, the database goes to that index first and then
retrieves the corresponding table records directly.
What is an Alias in SQL?
It is a temporary name assigned to the table or table column for the purpose of a
particular SQL query.

What is a View?
A view in SQL is a virtual table which is represent result-set of an SQL statement. A
view contains rows and columns, just like a real table.

Mahesh Kumar S
 17

Advantages of views
Security
 Views are used for security purpose in databases. View restricting the user's
to access the stored data
What are the DELETE, TRUNCATE and DROP statements?

DROP command is used to remove an object from the database. If you drop a table,
all the rows in the table is deleted and the table structure is removed from the
database.

What are Aggregate and Scalar functions?

An aggregate function returns a single value. Aggregate functions are often used with
the GROUP BY and HAVING clauses of the SELECT statement. Following are the widely
used SQL aggregate functions:
 COUNT() - Counts the total number of records in a specific table or view.
 SUM() - Calculates the sum of a collection of values.
 AVG() - Calculates the mean of a collection of values.
 MIN() - Calculates the minimum of a collection of values.
 MAX() - Calculates the maximum of a collection of values.
 FIRST() - Fetches the first element in a collection of values.
 LAST() - Fetches the last element in a collection of values.

Mahesh Kumar S
 18

Note: All aggregate functions described above ignore NULL values except for the
COUNT function.

Depending on the argument, COUNT() considers rows that meet certain conditions:

 The notation COUNT(*) includes NULL values in the total.

 The notation COUNT(column_name) only considers rows where the column
contains a non-NULL value.
 You can also combine COUNT with the DISTINCT operator to eliminate
duplicates before counting, and to count the combinations of values across
multiple columns.

Find null values in a column:

Mahesh Kumar S
 19

Count all rows which have no null value in columns

How to create empty tables with the same structure as another table?
Creating empty tables with the same structure can be done smartly by fetching the
records of one table into a new table using the INTO operator while fixing
a WHERE clause to be false for all records. Hence, SQL prepares the new table with a
duplicate structure to accept the fetched records but since no records get fetched due
to the WHERE clause in action, nothing is inserted into the new table.

What is Pattern Matching in SQL?

The SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a
column.

There are two wildcards often used in conjunction with the LIKE operator:

 % - The percent sign represents zero, one, or multiple characters

 _ - The underscore represents a single character

Mahesh Kumar S
 20

This kind of SQL query uses wildcards to match a string pattern, rather than writing
the exact word.
1. Using the % wildcard to perform a simple search
The % wildcard matches zero or more characters .Search a student in your database
with first name beginning with the letter K:

Omitting the patterns using the NOT keyword

Use the NOT keyword to select records that don't match the pattern. This query
returns all students whose first name does not begin with K.

Matching a pattern anywhere using the % wildcard twice

Search for a student in the database where he/she has a Q in his/her first name.

Using the _ wildcard to match pattern at a specific position

The _ wildcard matches exactly one character of any type. It can be used in
conjunction with % wildcard. This query fetches all students with letter K at the third
position in their first name.

Matching patterns for specific length

The _ wildcard plays an important role as a limitation when it matches exactly one
character. It limits the length and position of the matched results. For example –

Mahesh Kumar S
 21

Database creation:

https://paiza.io/projects/ufZgt5frpdMRH-uNVm2tuQ?language=mysql

Mahesh Kumar S
 22

https://www.techonthenet.com/sql/count_try_sql.php

Mahesh Kumar S
 23

Char vs varchar:
A CHAR field is a fixed length, and VARCHAR is a variable length field.
This means that the storage requirements are different - a CHAR always takes the
same amount of space regardless of what you store, whereas the storage
requirements for a VARCHAR vary depending on the specific string stored.

Imp SQL queries:

Mahesh Kumar S
 24

Select first 3 characters of FIRST_NAME from EMPLOYEE

Get length of FIRST_NAME from employee table

:select length(FIRST_NAME) from employee

Mahesh Kumar S
 25

Get employee details from employee table whose joining year is “2013”,month is
January.

Select * from EMPLOYEE where year(joining_date)='2013'

Select * from EMPLOYEE where month(joining_date)='01'

Get employee details from employee table who joined before January 1st 2013

Mahesh Kumar S
 26

(Format - “YYYY-DD-MM”), Select * from EMPLOYEE where joining_date <'2013-01-

01'

Get Last Name from employee table after replacing special character with white
space

Select REPLACE(LAST_NAME,'%',' ') from employee

43. Get department,no of employees in a department,total salary with respect to a

department from employee table order by total salary descending
Select DEPARTMENT,count(FIRST_NAME),sum(SALARY) Total_Salary from employee

group by DEPARTMENT order by Total_Salary desc

Mahesh Kumar S
 27

Mahesh Kumar S
 28

What is SQL Injection ?

SQL Injection is one of the the techniques uses by hackers to hack a website by

injecting SQL commands in data fields.

Mahesh Kumar S
 29

Mahesh Kumar S
 30

Nth highest salary:

select * from Employee ORDER BY `sal` DESC limit 5,1; // will return 6th highest

Mahesh Kumar S
 31

SQL Query to find second highest salary of Employee

Select top n record from the database: (mysql)

SELECT * FROM Customers LIMIT 3;
The SQL MIN() and MAX() Functions
finds the price of the cheapest product:
SELECT productName,MIN(Price) AS SmallestPrice FROM Products
The SQL COUNT(), AVG() and SUM() Functions

The COUNT() function returns the number of rows that matches a specified criterion.

The AVG() function returns the average value of a numeric column.

The SUM() function returns the total sum of a numeric column.

SELECT COUNT(productid),SUM(price),avg(price) FROM products.

The SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

Mahesh Kumar S
 32

The SQL BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be
numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

The SQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary
rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN,
SUM, AVG) to group the result-set by one or more columns.

Mahesh Kumar S
 33

The SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be
used with aggregate functions.

The following SQL statement lists the number of customers in each country. Only
include countries with more than 5 customers:

SQL EXISTS Operator

The following SQL statement returns TRUE and lists the suppliers with a product
price less than 20:

The SQL ANY and ALL Operators

The ANY and ALL operators are used with a WHERE or HAVING clause.

The ANY operator returns true if any of the subquery values meet the condition.

Mahesh Kumar S
 34

The ALL operator returns true if all of the subquery values meet the condition.

The following SQL statement returns TRUE and lists the product names if ALL the
records in the OrderDetails table has quantity = 10 (so, this example will return
FALSE, because not ALL records in the OrderDetails table has quantity = 10):

SQL NULL Functions

Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values.

Look at the following SELECT statement:

Mahesh Kumar S
 35

The SQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one
condition:

The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set in ascending or descending

order.

The ORDER BY keyword sorts the records in ascending order by default.

Mahesh Kumar S
 36

Comments in sql:
Multi-line comments :
/* comment */.
Single line command:
--Select all

Procedure:
A procedure is a combination of SQL statements written to perform a specified tasks.
It helps in code re-usability and saves time and lines of code.
We can return 0 to n values. However, we can pass values as parameters.

Mahesh Kumar S
 37

2. Triggers :
A trigger is a special kind of procedure which executes only when some
triggering event such as INSERT, UPDATE, DELETE .

Mahesh Kumar S
 38

Above SQL statement will create a trigger in the student database in which whenever
subjects marks are entered, before inserting this data into the database, trigger will
compute those two values and insert with the entered values.

TRIGGERS PROCEDURES

A Trigger is implicitly A Procedure is explicitly called by

invoked whenever any user/application using statements

event such as INSERT, or commands such as exec,

DELETE, UPDATE occurs in EXECUTE and enable the code

a TABLE. reusability.

Mahesh Kumar S
 39

Triggers are used to Procedures are used to perform

maintain referencial tasks defined or specified by the

integrity by keeping a users.

record of activities

performed on the table.

We cannot return values We can return 0 to n values.

in a trigger. Also, as an However, we can pass values as

input, we cannot pass parameters.

values as a parameter.

https://artoftesting.com/sql-queries-for-interview
http://a4academics.com/interview-questions/53-database-and-sql/397-top-100-
database-sql-interview-questions-and-answers-examples-
queries?showall=&limitstart=
https://www.edureka.co/blog/interview-questions/sql-query-interview-questions
https://medium.com/@ghumare64/list-of-important-sql-queries-for-interview-
32a86fe492b1

Mahesh Kumar S