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