A Database is one which has 1 or more tables.
A Table is the actual data with rows and columns. Table has a unique name. SQL is used to interact with database.
SQL -- Structured Query Language, its not case sensitive.
SQL commands comes under the following catagories
1. DML -- Data Manipulation Language
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
2. DDL -- Data Definitaion Language
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
3. DCL -- Data Control Language
GRANT - SQL GRANT is a command used to provide access or privileges on the database objects to the users
REVOKE - command removes user access rights or privileges to the database objects.
4. TCL -- Transactional Control Language
COMMIT - it successfully saves the transaction that we did.
ROLLBACK - is used to revert the transactions that we did before.
SQL COMMANDS
SELECT : -- is used to extract the data from db
Syntax : SELECT colName FROM tableName;
DISTINCT: -- is used to select distinct data from column if any duplicate data exists.
Syntax: SELECT DISTINCT colName FROM tableName;
WHERE: -- is used to extract only those records that fulfill a specified criterion.
Syntax: SELECT colName FROM tableName WHERE colName operator Value;
Operator can be =, <> , >, <, >=, <=, BETWEEN, LIKE, IN ORDER BY: -- is used to sort the result-set by a specified column. Syntax: SELECT colName FROM tableName ORDER BY colName ASC or DESC
INSERT INTO: statement is used to insert a new row in a table. Syntax: INSERT INTO table_name VALUES (value1, value2, value3,...) We can Insert data in specified columns as Eg: INSERT INTO Persons (P_Id, LastName, FirstName) VALUES (5, 'Tjessem', 'Jakob') ;
UPDATE: -- is used to update existing records in a table.
Syntax: UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value
DELETE: is used to delete records / rows in a table.
Syntax: DELETE FROM tableName WHERE colName = othercolName;
To delete all records from a table, then give below command
DELETE * FROM table_name
TOP: TOP clause is used to specify the number of records to return.
Syntax: SELECT TOP number|percent column_name(s) FROM table_name
Eg1 : SELECT TOP 2 * FROM tableName; -- returns top 2 records from table.
Eg2: SELECT TOP 50 PERCENT * FROM tableName; -- returns top 50 % records from table.
Eg3: SELECT * FROM Persons WHERE ROWNUM <=5 -- returns top 5 records from table. (IN ORACLE)
Eg4: SELECT * FROM Persons LIMIT 5 -- returns top 5 records from table. ( IN MYSQL)
LIKE: operator is used to search for a specified pattern in a column.
Syntax: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
Eg1: SELECT empName FROM Employee WHERE empName LIKE 'a%' --- selects employee names starting with a .
Eg2: SELECT empName FROM Employee WHERE empName LIKE '%a' --- selects employee names ending with a .
Eg3: SELECT empName FROM Employee WHERE empName LIKE '%ab%' --- selects employee names which has ab in their names .
Eg4: SELECT empName FROM Employee WHERE empName NOT LIKE 'b%' --- selects employee names which doesn't starts with b.
SQL Wildcards: -- can be used when searching for data in a database.
% -- percentage -- matches zero or more characters
_ -- underscore -- matches exactly one character
Eg: SELECT * FROM Persons WHERE FirstName LIKE '_la' --- this will return matching single character at begining.
[charlist] -- matches with list of characters
Eg: SELECT * FROM Persons WHERE LastName LIKE '[bsp]%'--- this will return LastNames starting with b or s or p.
IN Operator: -- allows you to specify multiple values in a WHERE clause.
Syntax: SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
Eg: SELECT LastName FROM Persons WHERE LastName IN ('kareem', 'khan') -- this will select LastName from Persons who have last name as karee and khan.
BETWEEN Operator: -- BETWEEN operator is used in a WHERE clause to select a range of data between two values.
Syntax: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
Eg1: SELECT * FROM Persons WHERE LastName BETWEEN 'kareem' AND 'khan' ; -- Selects LastName between kareem and khan.
Eg2: SELECT * FROM Persons WHERE LastName NOT BETWEEN 'kareem' AND 'khan' ; -- Selects all LastName except kareem and khan.
Alias: You can give a table or a column another name by using an alias
Syntax: SELECT column_name(s) FROM table_name AS alias_name --- for tables Syntax: SELECT column_name AS alias_name FROM table_name --- for columns
JOINS: -- are used to extract the data from 2 or more number of tables. While extracting data from different tables, each table will have kyes to relate each other. Primary Key is a column with unique value for each row.There are different types of joins
1. JOIN / INNER JOIN -- Return rows when there is at least one match in both tables
2. LEFT JOIN -- Return all rows from the left table, even if there are no matches in the right table
3. RIGHT JOIN -- Return all rows from the right table, even if there are no matches in the left table
4. FULL JOIN -- Return all rows from left and right tables when there is a match in one of the tables
UNION: -- UNION operator is used to combine the result-set of two or more SELECT statements.
Syntax: SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
Eg: SELECT e_EuroName FROM European UNION SELECT e_INDIA FROM INDIAN --- returns only distinct values, it wont return duplicate values.
Column_name should of same data type and columns should be in same order in the tables on which we perform UNION operation.
Syntax: SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
Eg: SELECT e_EuroName FROM European UNION ALL SELECT e_INDIA FROM INDIAN --- returns all values even if there are mutilple values with same value.
CREATE: -- is used to create a table in a database.
Syntax: CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, .... )
Eg: CREATE TABLE employee ( EName varchar2(20), EmpID int(5), EmpAddr varchar20(30) )
SQL CONSTRAINTS: -- are used to limit the type of data that can go into a table. These constraints will be given while creating table or after creating tables.
NOT NULL
UNIQUE KEY
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT -- are constraints
1. NOT NULL :- By default a table column can hold NULL values. So while creating a table specifying NOT NULL will make users wont accept NULL Values anymore
Eg: CREATE TABLE employee ( EName varchar2(20) NOT NULL, EmpID int(5) NOT NULL, EmpAddr varchar20(30) )
2. UNIQUE :- this constraint uniquely identifies each record in a database table.
UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
Eg: CREATE TABLE employee ( EName varchar2(20) NOT NULL, EmpID int(5) NOT NULL UNIQUE, EmpAddr varchar20(30) )
Whenever a table has been created without unique constraint, then through ALTER command we can create UNIQUE constraint as follows:
Eg: ALTER TABLE employee ADD UNIQUE (EmpID)
If you want to drop unique constraint in the table, then
Eg: ALTER TABLE employee DROP CONSTRAINT EmpID
3. PRIMARY KEY :- uniquely identifies each record in a database table.
Primary keys should contain unique values.
A Primary key column cannot contain NULL values. Each table should have a primary key. and each table can have only one primary key.
Eg: CREATE TABLE persons ( P_Id int NOT NULL, L_name varchar(100) NOT NULL, F_name varchar(100), city varchar(100), PRIMARY_KEY(P_Id) );
If want to ALTER the table then
ALTER TABLE persons ADD PRIMARY KEY (P_Id);
If want to DROP the table then
ALTER TABLE persons DROP PRIMARY KEY;
4. FOREIGN KEY :- is one which points to a PRIMAY KEY in another table.
Eg: CREATE TABLE Orders (O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id));
If we want to ALTER the table
ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ;
If we want to DROP the table
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;
5. CHECK :- is used to limit the value range that can be placed in a column.
If CHECK is defined on a single column, then it allows only certain values for this column.
If CHECK is defined on a table, it can limit the values in certain columns based on values in other columns in the row.
Eg: CREATE TABLE Persons ( P_Id int NOT NULL, L_name varchar(30) NOT NULL, F_name varchar(30), City varchar(30), CHECK(P_Id>0) ) ;
the above will accepts the input values in P_Id which should be greater than 0 (Zero).
If we want to ALTER the table
ALTER TABLE Persons ADD CHECK (P_Id>0) ;
If we want to DROP the table
ALTER TABLE Persons DROP CONSTRAINT check_persons;
6. DEFAULT :- is used to insert a default value into a column. The default value will be added to all the records in the table.
Eg: CREATE TABLE Persons
( P_Id int NOT NULL,
L_name varchar(30) NOT NULL,
F_name varchar(30),
city varchar(30) DEFAULT 'Bangalore' );
the above will create table Persons with city column for all persons as Bangalore.
If we want to ALTER the table
ALTER TABLE Persons ADD COLUMN City SET DEFAULT 'Bangalore';
If we want to DROP the table
ALTER TABLE Persons DROP City DEFAULT;
SQL ALTER COMMAND: is used to add/delete/modify the columns in an existing table.
the below command is used to ADD a new column DateOFBirth with Date as datatype.
Eg: ALTER TABLE Persons ADD DateOFBirth date;
the below command is used to MODIFY the datatype of the column
Eg: ALTER TABLE Persons ALTER COLUMN DateOFBirth Year;
the below is used to DROP the column
Eg: ALTER TABLE Persons DROP COLUMN DateOFBIrth;
AUTO INCREMENT : used to increment value in column automatically. By default it will increment from 1.
Eg: CREATE TABLE Persons
( P_Id int NOT NULL AUTO_INCREMENT,
L_name varchar(30) NOT NULL,
F_name varchar(30),
city varchar(30),
PRIMARY KEY(P_Id));
If we want to increment value other than 1 , say increment from 100
ALTER TABLE Persons AUTO_INCREMENT =100;
DATA TYPES IN SQL:- mostly there are 3 main types : text, number and Date/time types.
CHAR(size) -- Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size) --Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
INT(size) -- -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
DATE() -- A date. Format: YYYY-MM-DD
SQL FUNCTIONS: -- are the functions which returns a single value, calculated from values in a column.
The below are Aggregate functions:
1. AVG() -- returns the average value.
2. COUNT() -- returns number of rows.
3.MAX() -- returns the largest value
4.MIN() -- returns the smallest value
5.SUM() --- returns the sum of all columns
6.FIRST() -- returns the first value
7.LAST() -- returns the last value
The below are Scalar functions:
1. UCASE() -- converts a field to uppercase
2. LCASE() -- converts a field to lowecase
3. LEN() -- returns the length of a text field
4. ROUND() -- round the numeric field to the number of decimals specified.
5. FORMAT() -- formats how a field is to be displayed.
A Table is the actual data with rows and columns. Table has a unique name. SQL is used to interact with database.
SQL -- Structured Query Language, its not case sensitive.
SQL commands comes under the following catagories
1. DML -- Data Manipulation Language
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
2. DDL -- Data Definitaion Language
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
3. DCL -- Data Control Language
GRANT - SQL GRANT is a command used to provide access or privileges on the database objects to the users
REVOKE - command removes user access rights or privileges to the database objects.
4. TCL -- Transactional Control Language
COMMIT - it successfully saves the transaction that we did.
ROLLBACK - is used to revert the transactions that we did before.
SQL COMMANDS
SELECT : -- is used to extract the data from db
Syntax : SELECT colName FROM tableName;
DISTINCT: -- is used to select distinct data from column if any duplicate data exists.
Syntax: SELECT DISTINCT colName FROM tableName;
WHERE: -- is used to extract only those records that fulfill a specified criterion.
Syntax: SELECT colName FROM tableName WHERE colName operator Value;
Operator can be =, <> , >, <, >=, <=, BETWEEN, LIKE, IN ORDER BY: -- is used to sort the result-set by a specified column. Syntax: SELECT colName FROM tableName ORDER BY colName ASC or DESC
INSERT INTO: statement is used to insert a new row in a table. Syntax: INSERT INTO table_name VALUES (value1, value2, value3,...) We can Insert data in specified columns as Eg: INSERT INTO Persons (P_Id, LastName, FirstName) VALUES (5, 'Tjessem', 'Jakob') ;
UPDATE: -- is used to update existing records in a table.
Syntax: UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value
DELETE: is used to delete records / rows in a table.
Syntax: DELETE FROM tableName WHERE colName = othercolName;
To delete all records from a table, then give below command
DELETE * FROM table_name
TOP: TOP clause is used to specify the number of records to return.
Syntax: SELECT TOP number|percent column_name(s) FROM table_name
Eg1 : SELECT TOP 2 * FROM tableName; -- returns top 2 records from table.
Eg2: SELECT TOP 50 PERCENT * FROM tableName; -- returns top 50 % records from table.
Eg3: SELECT * FROM Persons WHERE ROWNUM <=5 -- returns top 5 records from table. (IN ORACLE)
Eg4: SELECT * FROM Persons LIMIT 5 -- returns top 5 records from table. ( IN MYSQL)
LIKE: operator is used to search for a specified pattern in a column.
Syntax: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
Eg1: SELECT empName FROM Employee WHERE empName LIKE 'a%' --- selects employee names starting with a .
Eg2: SELECT empName FROM Employee WHERE empName LIKE '%a' --- selects employee names ending with a .
Eg3: SELECT empName FROM Employee WHERE empName LIKE '%ab%' --- selects employee names which has ab in their names .
Eg4: SELECT empName FROM Employee WHERE empName NOT LIKE 'b%' --- selects employee names which doesn't starts with b.
SQL Wildcards: -- can be used when searching for data in a database.
% -- percentage -- matches zero or more characters
_ -- underscore -- matches exactly one character
Eg: SELECT * FROM Persons WHERE FirstName LIKE '_la' --- this will return matching single character at begining.
[charlist] -- matches with list of characters
Eg: SELECT * FROM Persons WHERE LastName LIKE '[bsp]%'--- this will return LastNames starting with b or s or p.
IN Operator: -- allows you to specify multiple values in a WHERE clause.
Syntax: SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
Eg: SELECT LastName FROM Persons WHERE LastName IN ('kareem', 'khan') -- this will select LastName from Persons who have last name as karee and khan.
BETWEEN Operator: -- BETWEEN operator is used in a WHERE clause to select a range of data between two values.
Syntax: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
Eg1: SELECT * FROM Persons WHERE LastName BETWEEN 'kareem' AND 'khan' ; -- Selects LastName between kareem and khan.
Eg2: SELECT * FROM Persons WHERE LastName NOT BETWEEN 'kareem' AND 'khan' ; -- Selects all LastName except kareem and khan.
Alias: You can give a table or a column another name by using an alias
Syntax: SELECT column_name(s) FROM table_name AS alias_name --- for tables Syntax: SELECT column_name AS alias_name FROM table_name --- for columns
JOINS: -- are used to extract the data from 2 or more number of tables. While extracting data from different tables, each table will have kyes to relate each other. Primary Key is a column with unique value for each row.There are different types of joins
1. JOIN / INNER JOIN -- Return rows when there is at least one match in both tables
2. LEFT JOIN -- Return all rows from the left table, even if there are no matches in the right table
3. RIGHT JOIN -- Return all rows from the right table, even if there are no matches in the left table
4. FULL JOIN -- Return all rows from left and right tables when there is a match in one of the tables
UNION: -- UNION operator is used to combine the result-set of two or more SELECT statements.
Syntax: SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
Eg: SELECT e_EuroName FROM European UNION SELECT e_INDIA FROM INDIAN --- returns only distinct values, it wont return duplicate values.
Column_name should of same data type and columns should be in same order in the tables on which we perform UNION operation.
Syntax: SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
Eg: SELECT e_EuroName FROM European UNION ALL SELECT e_INDIA FROM INDIAN --- returns all values even if there are mutilple values with same value.
CREATE: -- is used to create a table in a database.
Syntax: CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, .... )
Eg: CREATE TABLE employee ( EName varchar2(20), EmpID int(5), EmpAddr varchar20(30) )
SQL CONSTRAINTS: -- are used to limit the type of data that can go into a table. These constraints will be given while creating table or after creating tables.
NOT NULL
UNIQUE KEY
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT -- are constraints
1. NOT NULL :- By default a table column can hold NULL values. So while creating a table specifying NOT NULL will make users wont accept NULL Values anymore
Eg: CREATE TABLE employee ( EName varchar2(20) NOT NULL, EmpID int(5) NOT NULL, EmpAddr varchar20(30) )
2. UNIQUE :- this constraint uniquely identifies each record in a database table.
UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
Eg: CREATE TABLE employee ( EName varchar2(20) NOT NULL, EmpID int(5) NOT NULL UNIQUE, EmpAddr varchar20(30) )
Whenever a table has been created without unique constraint, then through ALTER command we can create UNIQUE constraint as follows:
Eg: ALTER TABLE employee ADD UNIQUE (EmpID)
If you want to drop unique constraint in the table, then
Eg: ALTER TABLE employee DROP CONSTRAINT EmpID
3. PRIMARY KEY :- uniquely identifies each record in a database table.
Primary keys should contain unique values.
A Primary key column cannot contain NULL values. Each table should have a primary key. and each table can have only one primary key.
Eg: CREATE TABLE persons ( P_Id int NOT NULL, L_name varchar(100) NOT NULL, F_name varchar(100), city varchar(100), PRIMARY_KEY(P_Id) );
If want to ALTER the table then
ALTER TABLE persons ADD PRIMARY KEY (P_Id);
If want to DROP the table then
ALTER TABLE persons DROP PRIMARY KEY;
4. FOREIGN KEY :- is one which points to a PRIMAY KEY in another table.
Eg: CREATE TABLE Orders (O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id));
If we want to ALTER the table
ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ;
If we want to DROP the table
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;
5. CHECK :- is used to limit the value range that can be placed in a column.
If CHECK is defined on a single column, then it allows only certain values for this column.
If CHECK is defined on a table, it can limit the values in certain columns based on values in other columns in the row.
Eg: CREATE TABLE Persons ( P_Id int NOT NULL, L_name varchar(30) NOT NULL, F_name varchar(30), City varchar(30), CHECK(P_Id>0) ) ;
the above will accepts the input values in P_Id which should be greater than 0 (Zero).
If we want to ALTER the table
ALTER TABLE Persons ADD CHECK (P_Id>0) ;
If we want to DROP the table
ALTER TABLE Persons DROP CONSTRAINT check_persons;
6. DEFAULT :- is used to insert a default value into a column. The default value will be added to all the records in the table.
Eg: CREATE TABLE Persons
( P_Id int NOT NULL,
L_name varchar(30) NOT NULL,
F_name varchar(30),
city varchar(30) DEFAULT 'Bangalore' );
the above will create table Persons with city column for all persons as Bangalore.
If we want to ALTER the table
ALTER TABLE Persons ADD COLUMN City SET DEFAULT 'Bangalore';
If we want to DROP the table
ALTER TABLE Persons DROP City DEFAULT;
SQL ALTER COMMAND: is used to add/delete/modify the columns in an existing table.
the below command is used to ADD a new column DateOFBirth with Date as datatype.
Eg: ALTER TABLE Persons ADD DateOFBirth date;
the below command is used to MODIFY the datatype of the column
Eg: ALTER TABLE Persons ALTER COLUMN DateOFBirth Year;
the below is used to DROP the column
Eg: ALTER TABLE Persons DROP COLUMN DateOFBIrth;
AUTO INCREMENT : used to increment value in column automatically. By default it will increment from 1.
Eg: CREATE TABLE Persons
( P_Id int NOT NULL AUTO_INCREMENT,
L_name varchar(30) NOT NULL,
F_name varchar(30),
city varchar(30),
PRIMARY KEY(P_Id));
If we want to increment value other than 1 , say increment from 100
ALTER TABLE Persons AUTO_INCREMENT =100;
DATA TYPES IN SQL:- mostly there are 3 main types : text, number and Date/time types.
CHAR(size) -- Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size) --Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
INT(size) -- -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
DATE() -- A date. Format: YYYY-MM-DD
SQL FUNCTIONS: -- are the functions which returns a single value, calculated from values in a column.
The below are Aggregate functions:
1. AVG() -- returns the average value.
2. COUNT() -- returns number of rows.
3.MAX() -- returns the largest value
4.MIN() -- returns the smallest value
5.SUM() --- returns the sum of all columns
6.FIRST() -- returns the first value
7.LAST() -- returns the last value
The below are Scalar functions:
1. UCASE() -- converts a field to uppercase
2. LCASE() -- converts a field to lowecase
3. LEN() -- returns the length of a text field
4. ROUND() -- round the numeric field to the number of decimals specified.
5. FORMAT() -- formats how a field is to be displayed.
No comments:
Post a Comment