BASICS OF SQL

DATABASE: [DB]

• Used for storing the data.
• Variables are also data storage. But variables are temporary data storage.
• Database are permanent data storage.
• Database storing the data in the structure of TABLE (ROWS & COLUMNS).
  • Rows are known as RECORDS
  • Columns are known as FIELDS

RDBMS:

• RELATIONAL DATABASE MANAGEMENT SYSTEM
• Relationship between data are known as RDBMS.

NAME	MARKS
Rajii	70
Lakshmi	80
Mani	90

• Rajii & 70 –> These are in relational. Because they are placed in same row.
• Above 6 data are in relational. Because they are placed in same table.
• This is Relational Database.

SQL:

• STRUCTURED QUERY LANGUAGE
• This language is useful for communicate with relational database.
• SQL is Used for accessing and manipulating the database.
• In SQL, Code must be in query format.
• SQL consist of below languages.
  • DDL
  • DML
• Oracle, MySQL, MS SQL Server, DB2, H2, SQLite these are SQL.

DATA DEFINITION LANGUAGE: [DDL]

• Create table
• Alter table
• Drop table
• Truncate table

DATA MANIPULATION LANGUAGE: [DML]

• Select Database
• Insert Database
• Delete Database
• Update Database

---

SQL Syntax:

1] CREATE:

• CREATE command is used to create new database & new table.
• During table creation, must include columns with datatype.

Syntax:

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype
);

CREATE TABLE "Students" (
	"Name"	 TEXT,
	"MailId" TEXT,
	"RollNo" INTEGER,
	"Age"	 INTEGER
);

Output:

---

2] INSERT

• INSERT command is used to insert the data in the table.

Syntax:

INSERT INTO table_name (column1, column2, column3....)  
VALUES (value1, value2, value3.....); 

INSERT INTO Students(Name, MailId, RollNo, Age)  
VALUES ('Mani', 'm@gmail.com', 9526, 24); 

Output:

INSERT MULTIPLE LINES:

Syntax:

INSERT INTO table_name (column1, column2, column)  
VALUES (value1, value2, value3),(value4, value5, value6),(value7, value8, value9); 

INSERT INTO Students(Name, MailId, RollNo, Age)  
VALUES ('Mani', 'm@gmail.com', 9526, 24),
('Rajii', 'r@gmail.com', 1410, 30),
('Lakshmi', 'l@gmail.com', 6020, 27),
('Arjun', 'a@gmail.com', 1111, 2); 

Output:

---

3] UPDATE:

• It is used to change the data in the table.

Syntax:

UPDATE table_name SET column_name1= value1
WHERE condition 

UPDATE Students SET RollNo= 9527
WHERE Name = 'Arjun'

Output:

---

4] ALTER:

• It is used to add or modify columns in the table

Syntax:

ALTER TABLE table_name ADD column_name column_datatype

ALTER TABLE Students ADD Class int

Output:

---

5] DELETE:

• Delete is used to delete the rows in the table.

Syntax:

DELETE FROM table_name WHERE condition;  

DELETE FROM Students WHERE Age = '2';

Output:

• Delete all rows

Syntax:

DELETE FROM table_name 

DELETE FROM Students 

Output:

---

6] SELECT:

• Select Commands denotes SHOW
• * denotes all

1] Show full table:

Syntax:

SELECT * FROM table_name; 

SELECT * FROM Students

Output:

2] Show particular column:

Syntax:

SELECT column FROM Students

SELECT Name FROM Students

Output:

3] Show particular row:

Syntax:

SELECT * FROM Students WHERE Condition

SELECT * FROM Students WHERE Name = 'Mani'

Output:

---

7] TRUNCATE:

• It delete all the data rows in the table.
• It can’t delete the heading row in the table.

Syntax:

TRUNCATE TABLE table_name

TRUNCATE TABLE Students

Output: [DOUBT]

Result: near “TRUNCATE”: syntax error

---

8] DROP:

• It delete the table completely.
• It deletes all data rows & heading rows & table structure.
• We can use this syntax for deleting the database also.

Syntax:

DROP TABLE table_name;

DROP TABLE Students;

---

Delete Vs Truncate Vs Drop:

DELETE	TRUNCATE	DROP
DML	DDL	DDL
Used to delete particular data row in the table.	Used to delete all the data rows in the table.	Used to delete entire table. Table Structure also deleted.

---

SQL SELECT:

1] DISTINCT

DISTINCT is used to showing only unique data.

i.e., It removes duplicate values.

• Ex: 1
• Below table we have “Mani” two times.

Syntax:

SELECT DISTINCT column_name FROM table_name;

SELECT DISTINCT Name FROM Students;

Output:

• Ex: 2
• Below table we have same data in row 2 & row 3.

Syntax:

SELECT DISTINCT * FROM table_name;

SELECT DISTINCT * FROM Students;

Output:

---

2] AS

• Using AS, we have changed the columns heading.

Syntax:

SELECT column_name AS new_name_for_column FROM table_name

SELECT Name AS Students_Name, MailId AS Students_MailID, RollNo AS Students_RollNo, Age AS Students_Age FROM Students

Output:

---

Note: If we need space use set bracket

SELECT Name AS [Students Name] FROM Students

Output:

---

3] ASCENDING & DESCENDING ORDER:

• ORDER BY keyword is used here.
• If we need ascending order mention ASC.
• If we need descending order mention DESC.
• If we didn’t mention ASC or DESC in syntax, ORDER BY take ASC as default.

ASCENDING Syntax:

SELECT * FROM table_name ORDER BY column_name ASC

SELECT * FROM Students ORDER BY Age ASC

Output:

DESCENDING SYNTAX:

SELECT * FROM table_name ORDER BY column_name DESC

SELECT * FROM Students ORDER BY Age DESC

OUTPUT:

---

CONSTRAINTS:

• Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.
• Constraints are:
  • NOT NULL : Ensures that a column cannot have a NULL value
  • PRIMARY KEY: Combination of a NOT NULL and UNIQUE.
  • FOREIGN KEY: It refers to the Another table primary key.
  • UNIQUE: Ensures that all values in a column are different
  • DEFAULT: if no input value is specified, Sets a default value for a column.
    

Ex:

CREATE TABLE "Employee" 
(
"Name" TEXT NOT NULL,
"Id" INTEGER PRIMARY KEY,
"City" TEXT DEFAULT 'Chennai',
"Mble" INTEGER UNIQUE
);

Person Table:

PersonID	LastName	FirstName	Age
1	Hansen	Ola	30
2	Svendson	Tove	23
3	Pettersen	Kari	20

Orders Table:

OrderID	OrderNumber	PersonID
1	77895	3
2	44678	3
3	22456	2
4	24562	1

• Persons Table –> PersonID ==> PRIMARY KEY
• Orders Table –> PersonID ==> FORIEGN KEY
• Foriegn Key & Primary Key are same in these two tables. Using these keys we can access one table to another table.

---

JOINS:

• JOIN is used to combine rows from two or more tables, based on a related column between them.
• Types:
  • INNER JOIN: It returns the common rows between tables.
  • FULL OUTER JOIN: It returns all the rows from comparing tables.
  • LEFT JOIN: Returns common rows between tables & all rows from left table
  • RIGHT JOIN: Returns common rows between tables & all rows from right table

https://www.w3schools.com/sql/sql_join.asp

Ex:

MEMBERS TABLE:

FEESAMOUNT TABLE:

Syntax:

SELECT table1.column, table2.column 
FROM table1 JOIN_TYPE table2
ON table1.primarykey_column = table2.foreignkey_column

• INNER JOIN:

SELECT Members.Name, Members.City, FeesAmount.FeesType, FeesAmount.Fees 
FROM Members INNER JOIN FeesAmount
ON Members.RollNo = FeesAmount.StudentID

Output:

• LEFT JOIN

SELECT Members.Name, Members.City, FeesAmount.FeesType, FeesAmount.Fees 
FROM Members LEFT JOIN FeesAmount
ON Members.RollNo = FeesAmount.StudentID

Output:

---

AUTO INCREMENT BASICS:

Ex1:

CREATE TABLE "Practice1" (
	"Name"	TEXT,
	"City"	TEXT,
	"RollNo"	INTEGER PRIMARY KEY AUTOINCREMENT
);

INSERT INTO Practice1(Name, City)  
VALUES ('Mani', 'Cmbe'),
('Rajii', 'Snkl'),
('Lakshmi', 'Chennai'),
('Arjun', 'Bangalore');

Select * from Practice1

Output:

Note:

• By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

---

Basics of Union & Union All:

• UNION: Removes duplicate records
• UNION ALL: Does not remove duplicate records

Ex:

Table 1: ID are 1, 2, 3, 4

Table 2: ID are 5, 6, 4, 3

UNION Output: 1, 2, 3, 4, 5, 6

UNION ALL Output: 1, 2, 3, 4, 5, 6, 4, 3

---

BASICS OF SQL FUNCTIONS:

• SQL have functions. These functions are called as Stored Procedure in SQL.

Syntax:

//Function Definition
CREATE PROCEDURE function_name
AS
SELECT * FROM table_name    // Based on need
GO;

//Function Call
EXEC function_name

• SQL have aggregate & scalar functions.

AGGREGATE FUNCTIONS:

This function performs a calculation for one or more values and returns a single value.

• AVG( )
• COUNT( )
• MAX( )
• MIN( )
• SUM( )

SCALAR FUNCTION:

This function performs on each row & returns a value for each row.

• UCASE( ) : Uppercase
• LCASE( ) : LowerCase
• LEN( ) : Length
• ROUND( ) : Round Value

---