edureka sql

Data Definition Language (DDL) Deals with database schemas and descriptions, of how the data should reside in the database.

Data Control Language (DCL) it includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system

Data Manipulation Language (DML) Deals with data manipulation and includes most common SQL statements. Such as INSERT, UPDATE

Transaction Control Language (TCL) deals with transaction within a database

Types of Database:

Distributed Database
Object Oriented Database
Centralized Database
Operational Database
Graph Database
NoSQL Database
Cloud Database
Relational Database

Popular Databases

mongoDB
PostgresSQL
Access
MS SQL Server
MySQL
Oracle DB

What is a table? Table is collection of data in a tabular form. Rows and Columns

Tuples and Attributes

Tables Constraints:

Check Default
Foreign Key
Index
Unique
Primary Key
Not Null

create table emp(

id int not null auto_increment,

ename varchar(20),

address varchar(30),

primary key(id)

);

drop table emp;

CREATE TABLE Student(

studentID INT NOT NULL AUTO_INCREMENT,

FName VARCHAR(20),

LName VARCHAR(20),

Address VARCHAR(30),

City VARCHAR(15),

Marks INT,

PRIMARY KEY(studentID)

);

select * from student;

select fname, lname from student;

SELECT FName

FROM Student

WHERE City='GOA';

SELECT * FROM Student

WHERE FName = 'John' OR

Lname = 'Doe';

INSERT INTO Student(Fname, Lname, Address, City, Marks)

VALUES('Manooj','Sharma','#07, MG Road', 'Jaipur',438)

Aggregate Functions

Count: this function returns the number of rows that match specified criteria

SELECT COUNT (studentID)

FROM Student;

Average: This function returns the average value of a numeric column

SELECT AVG(Marks)

FROM Student;

SUM: This function returns the total sum of a numeric column

SELECT SUM(Marks)

FROM Student;

MIN: This function returns the smallest value of the selected column

SELECT fname, lname, MIN(Marks)

FROM Student;

MAX This function returns the largest value of the selected column

SELECT fname, lname, MIN(Marks)

FROM Student;

GROUP BY: Used in SQL to arrange identical data into groups with the help of some functions

This query groups student by cities where they from

SELECT COUNT(StudentID), City

FROM Student

GROUP BY City;

HAVING: Used to place conditions where we need to decide which group will be the part of final result-set

This query find students sum marks are larger than 500 marks

SELECT Fname, SUM(Marks)

FROM student

GROUP BY fname

HAVING SUM(marks)>500;

ORDER BY: This keyword is used to sort the result-set in ascending or descending order

Descending order is higher alpha to

SELECT COUNT(studentID), city

FROM student

GROUP BY city

ORDER BY city desc;

NULL VALUES: IS NULL.

SELECT Fname, Lanme

FROM Student

WHERE Marks IS NOT NULL;

UPDATE: The Update command is used to modify rows in a table

UPDATE Student

SET Fname = 'Amar', Lname = 'Kumar'

WHERE StudentID = 1;

DELETE: The SQL DELETE command is used to delete rows that are no longer required from the database tables

DELETE FROM student

WHERE city = 'Mumbai';

IN OPERATOR: IN operator i sused to specify multiple values inside the WHERE clause. It acts as a short for multiple OR

SELECT StudentID, Fname, Lname

FROM Student

WHERE City IN ('Delhi', 'Goa', 'Pune', 'Bengaluru');

BETWEEN OPERATOR: BETWEEN operator will select a particular value within the specified range

SELECT StudentID, Fname, Lname FROM Student

WHERE Marks BETWEEN 400 AND 500;

ALIAS

This will rename table column from "studentID" to just "id"

SELECT studentID as id

FROM student;

Table Alias

SELECT S.Fname, S.Lname

FROM Student as S;

LIKE OPERATOR

SELECT dept_name, loc FROM dept WHERE loc LIKE 'c%';

SELECT dept_name, loc FROM dept WHERE loc LIKE 'chen_ _ _';

SELECT dept_name, loc FROM dept WHERE loc LIKE '%i';

SELECT dept_name, loc FROM dept WHERE loc LIKE 'c%i';

IF ELSE

SELECT CASE(LOC)

WHEN 'Chennai' THEN 'TamilNadu'

WHEN 'Bangalore' THEN 'KR'

ELSE 'NO IDEA'

END

FROM DEPT

What is normalization?

Processes of reducing the redundancy of data.

Improves the Data Integrity

This table had two telephone numbers

What is TRIGGER? Triggers are` the SQL codes that are automatically executed in response to certain events on a particular table. These are used to maintain the integrity of the data.

CREATE TRIGGER sample_trigger

before INSERT

ON student

FOR EACH ROW

SET new.marks = new.marks+6;

DROP TRIGGER sample_trigger;

SHOW TRIGGERS in edureka;

CREATE TRIGGER calculate

before INSERT

ON student

FOR EACH ROW

SET new.marks = new.marks+100;

What are Joins in SQL?

JOINS in SQL are commands which are used to combine rows from two or more tables based on a related column between those tables. They are predominantly used when a user is trying to extract data from tables which have one-to-many or many-to-many relationships between them.

INNER JOIN: This type of join returns those records which have matching values in both tables. So, if you perform an INNER JOIN operation between the Employee table and the Project table. all the tuples which have matching values in both the tables will be given as output.

SELECT Employee.EmpID, Employee.EmpFname, Employee.EmpLname, Project.ProjectID, Project.ProjectName

FROM Employee

INNER JOIN Project ON Employee.EmpID=Project.EmpID;

LEFT JOIN: The LEFT JOIN or the LEFT OUTER JOIN returns all the records from the left table and also those records which satisfy a condition from the right table. Also, for the records having no matching values in the right table, the output or the result-set will contain the NULL values

RIGHT JOIN: The RIGHT JOIN or the RIGHT OUTER JOIN returns all the records from the right table and also those records which satisfy a conditioons from the left table. Also, for the records having no matching values in the left table, the output or the result-set will contain the NULL values.

SELECT Employee.EmpID, Employee.EmpFname, Employee.EmpLname, Project.ProjectID, Project.ProjectName

FROM Employee

INNER JOIN Project ON Employee.EmpID=Project.EmpID;

FULL JOIN: FULL JOIN or the Full Outer Join returns all those records which either have a match in the left(Table1) or the right (Table2) table.

SELECT Employee.EmpID, Employee.EmpFname, Employee.EmpLname, Project.ProjectID, Project.ProjectName

FROM Employee

LEFT JOIN Project ON Employee.EmpID=Project.EmpID;

UNION

SELECT Employee.EmpID, Employee.EmpFname, Employee.EmpLname, Project.ProjectID, Project.ProjectName

FROM Employee

RIGHT JOIN Project ON Employee.EmpID=Project.EmpID;

What is a Natural Join and which situations is a Natural Join used?

How to map many-to-many relationships using joins?

What is a Hash Join?

Hash Joins are aslo a type of joins which are used to join large tables or in an instance, where the user wants most of the joined table rows. The Hash Join algorithm is a two-step algorithm.

What is Self and Cross Join?

Self Join: is a join of table to itself. This implies that each row in a table is joined with itself.

The Cross Join is a type of join in which a join clause is applied to each row of a table to every row of the other table. Also, when the WHERE condition is used, this type of JOIN behaves as an INNER JOIN, and when the WHERE condition is not present, it behaves like a CARTESIAN product.