SQL in one SHOT
NOTE:- DOWNLOAD MICROSOFT SQL SERVER TO PRACTICE.
Data types :-
Character string, Numeric, Date and Time, Binary.
SQL Commands :-
Creating database :- Database helps in storing and easily manupulating large data sources.
create database <database_name>
Creating table :-
create table <table_name> (columnA datatype, columnB datatype);
If we want to make a column to have a mandatory have a value, then we have to add NOT NULL after datatype.
create table <table_name> (columnA datatype NOT NULL);
Various Constraints :-
A. NOT NULL :-
If we want to make a column to have a mandatory have a value, then we have to add NOT NULL after datatype.
B. UNIQUE :-
No two rows can have the same values.
C. Primary Key :-
A column that uniquely identifies a row.
Alternate way of creating PK :-
We can also have more than one column as PK, but it is not possible to create them using method one, so we use method two :-
Primary Key vs Unique Key:-
- Primary key can not have null values, but Unique key can have.
2. A table can have only one primary key, but can have multiple unique keys.
D. Foreign key:-
Two tables can be related by means of primary key and foreign key.
Adding foreign key after table creation :-
Note :- Here we set up a relation of Parent-Child, so if we delete any data in Parent it must get reflected in child also, otherwise we will get an error. So to tell that on any changes in parent, child must get updated accordingly, we use “on Delete Cascade” in child table.
DML Commands:-
Insert Command:-
→ insert into <table_name> values(value1, value2);
→ insert into <table_name> (colA_name, colB_name) values(value1, value2);
Insert-As-Select Commands :-
Using this command, we insert the in table coming from another table. Creating two table and displaying data of table after inserting value to only one table.
insert into <new_table name> (new_table_col_names) (select old_table_col_names_comma_separated from <old_table_name> );
Note:- We can also add where condition to insert data from another table based on any condition.
Update Command:-
Used to update/modify the existing data of single or multiple columns in the table.
If we ignore where command, then all data will get updated.
update <tabel_name> set <field_name> = <value> where <condition>;
Delete Command :-
→ Delete rows/ record from database table, it can be executed with or without where condition, if where condition is not given then it will remove all the records from the table.
delete from <table_name> where condition;
After removing where condition:-
Select Command :-
→ Used to retrieve the data from table.
Retrieving data from Single table:-
select <field1, feild2,…> from table_name where condition;
Selecting top(x) items from selected items :-
Alias name/record :-
Naming id an alias name user_id.
Alias are mainly implement in joins, long table name, long column name also.
Select Distinct :-
Sorting Data :- order by
select <columns> from <table_name> [where <condition>] [order by <columns [asc, desc]>;
Filtering :-
A. Logical operator :- OR, NOT, AND.
→ select * from <table_name> where condition1 AND condition2;
→ select * from <table_name> where condition1 OR condition2;
→ select * from <table_name> where condition1 NOT condition2;
“ NOT fetches opposite of Result.”
B. Comparison Operator :- (=, !=, <>, ≥, ≤, LIKE, Between, IN)
→ Between :- Used to search between set of values.
select * from <table_name> where <column> between <value1> and <value2>;
→ IN :- used to fetch value from set of literals.
select * from <table_name> where <field> in (value1,value2,value3,value4);
→ Like :-
select * from <table_name> where name LIKE ‘a%’;
‘%a’ → name end with ‘a’ and can have anything before it.
‘a%’ → name starts with ‘a’ and can have anything after that.
‘%a%’ → name have ‘a’ in it and can have anything before and after that.
‘_ _ _ _’ → name length should be equal to 4
‘H_ _ _’ → name should start with ‘H’ and can have 3 more char after that.
‘a%i’ → name starts with ‘a’ and ends with ‘i’.
Retrieving data from Multiple table:- [use of JOINS → discussed later in this story] :-
Dropping table or column :-
DROP TABLE table_name;
ALTER TABLE Customers
DROP COLUMN ContactName;
Case Expression :-
Used as a type of if-then-else statement, CASE provides if-then-else type of logic to SQL.
Syntax:-
CASE column_name
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result
END
Ex:- Declaring a variable input and setting its value to 3 and using when to see what it’s value is.
Using CASE with Tables :-
Note :- Equal operator (‘=’) is not used with NULL.
Ex:- select * from Emp where salary = NULL → Gives no output
Ex:- select * from Emp where salary IS NULL → Gives correct output.
Ex:- select * from Emp where salary IS NOT NULL → Gives correct output.
Add/Drop constraint on existing table :-
A. To add constraint :-
NOTE:- constraint name should be meaningful and can be of different name also.
Suppose we have a table :-
(Note:- emp4_id is NOT NULL :- so it can be primary key)
In case it is NULL type then use above alter commands to change to NOT NULL first then .
B. To drop constraint :-
View :- A virtual table.
It takes the output of a query and treats it as a table.
SQL Joins :-
→ Used to fetch data from two or more tables, based on condition.
→ Types :-
A. Cross Join / Cartesian Products
B. Self Join
C. Inner Join → Equi Join, Natural Join
D. Left Join
E. Right Join
F. Full outer Join
A. Cross Join :- Display all the rows and all the columns of both the tables.
Syntax:-
select <column_list>
from <table_name1> t1
CROSS JOIN <table_name1> t2
ON t1.column_name = t2.column_name;
B. Self Join :- A table can be joined to itself.
Note:- In Self Join, we must use table alias.
Syntax:-
select <column_list>
from <table_name1> t1
JOIN <table_name1> t2
ON t1.column_name = t2.column_name;
Ex:-
We have a table :-
Emp :- emp_id, emp_name, manager_id, manager_name
We want to see name in table having emp_id = manager_id
select t2.emp_name as employee, t1.manager_name as Manager
from Emp t1 JOIN Emp t2
ON t1.emp_id = t2.manager_id;
C. Inner Join :- It fetches records from both table that satisfy the condition.
In below example :- It fetches records that have matching values in both tables.
Syntax:-
select <column_list>
from <table_name1> t1
INNER JOIN <table_name1> t2
ON t1.column_name = t2.column_name;
Practice Link :- https://www.w3schools.com/sql/sql_join.asp
Suppose we have two tables :- Customer and Orders, we want to fetch date , name, of customer who order something :-
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
In above CustomerID is common in both so we must access them via table name but we can directly access OrderId and CustomerName without specifying the table name.
Note:- In above example we applied ‘=’ as condition, so this is known as Equi Join.
Equi Join :- It is a type of Inner Join where we use equal operator as Constraint.
Natural Join :- It is a type of Equi Join which have same column names.
Left Outer Join :-
A. It returns rows to the left (t1) even if there are no rows on the right (t2) of the join clause.
B. The result will be NULL for rows on Right table,when there is no match.
That is Left outer join gives the all values from Left table plus values from right table which satisfy condition.
Syntax:-
select <column_list>
from <table_name1> as t1
LEFT OUTER JOIN <table_name2> as t2
ON t1.column_name = t2.column_name;
Practice :- LINK
Example 1 :-
Table food:-
Table company:-
SQL Query :-
In the output notice that all values from foods table are present, but only those values from company are there who satisfy the condition, here it is ‘=’.
So, company_id = 30 is not in food, so we didn’t get that in output.
Output:-
You can test above code and changes according to your experiment :-
Right outer Join :- A. It returns rows to the right (t2) even if there are no rows on the left (t1) of the join clause.
B. The result will be NULL for rows on Left table,when there is no match.
That is Right outer join gives the all values from Right table plus values from Left table which satisfy condition.
Syntax:-
select <column_list>
from <table_name1> as t1
RIGHT OUTER JOIN <table_name2> as t2
ON t1.column_name = t2.column_name;
Similar to LEFT Join code you can analyze the Right Join also.
CODE :- LINK
Full Outer Join :- It returns all records when there is a match in either left(t1) or right (t2) table recordes.
Syntax:-
select <column_list>
From <table_name1> as t1
FULL OUTER JOIN <table_name2> as t2
ON t1.column_name = t2.column_name;
Summary :-
SQL Built-in Functions :-
A. Conversion Function
B. Logical Functions
C. Math Functions
D. Aggregate Functions
E. String Functions
F. Date Functions
A. Conversion Function:-
B. Logical Functions :- [IMPO]
NOTE :- SQL is 1 based indexing.
C. Math Functions :-
D. Aggregate Functions :- [IMPO]
E. String Functions :-
F. Date Functions :-
DATEDIFF → mm :- means difference in Months.
Custom Functions :-
GROUP BY and HAVING :-
Group by :- Used to group the data in single column.
NOTE:- All the column in SELECT should be written after GROUP BY keyword.
Having :- It was added in SQL as we can’t use WHERE clause with Aggregate Function. HAVING is used with “summarized grouped records” but WHERE is used for “individual records”. Mostly HAVING is used with GROUP BY.
Stored Procedure :-
ALTER :- used when we want to make changes in existing Stored Procedure.
User Defined Functions:-
Example 1:-
Example 2 :-
Triggers :-
It is an object which automatically executes when an event occur in Database.
KEYS :-
There are mainly 7 types of Keys, that can be considered in a database. I am going to consider the below tables to explain to you the various keys.
- Candidate Key — This is a set of attributes that can uniquely identify a table. Each table can have more than a candidate key. Apart from this, out of all the candidate keys, one key can be chosen as the Primary key. In the above example, since CustomerID and PanNumber can uniquely identify every tuple, they would be considered as a Candidate Key.
- Super Key — This is a set of attributes that can uniquely identify a tuple. So, a candidate key, primary key, and a unique key is a superkey, but vice-versa isn’t true.
- Primary Key — This is a set of attributes that are used to uniquely identify every tuple. In the above example, since CustomerID and PanNumber are candidate keys, any one of them can be chosen as a Primary Key. Here CustomerID is chosen as the primary key.
- Unique Key — The unique key is similar to the primary key, but allows NULL values in the column. Here the PanNumber can be considered as a unique key.
- Alternate Key — Alternate Keys are the candidate keys, which are not chosen as a Primary key. From the above example, the alternate key is PanNumber
- Foreign Key — An attribute that can only take the values present as the values of some other attribute, is the foreign key to the attribute to which it refers. in the above example, the CustomerID from the Customers Table is referred to as the CustomerID from the Customer_Payment Table.
- Composite Key — A composite key is a combination of two or more columns that identify each tuple uniquely. Here, the CustomerID and Date_of_Payment can be grouped together to uniquely identify every tuple in the table.
Thanks For Reading….
Keep Coding and Exploring….
Refer to this for practicing Interview Questions :- LINK
