SQL -- STRUCTURED QUERY LANGUAGE
NORTH INDIAN --HINDI
SOUTH INDIAN --TELUGU, KANNADA
GLOBAL -- ENGLISH
1970-- SEQUEL BY IBM..
SELECT
CREATE DATABASE...
Attributes -- Dept, Location
Fact -- Sales, Profit, Discount...
------------------------------------------------------
Types of Commands
1. DDL --Data defination language
2. DML --Data Manipulation language
3. DQL --Data Query Language
4. TCL --Transaction Control Language
5. DCL --Data Control Language
Create a database for source and destination
Create source Tables and Destination Tables Schema
Inserting data into these tables
Modifying these tables...
New Attributes or columns
Update data to existing tables
Delete the columns...or tables or delete a record...
Select --- to analyse the existing data...
Controlling your data access....
1. Data Defination Language...
CREATE
DROP
ALTER
TRUNCATE
2. DML
INSERT
UPDATE
DELETE
INT -- INTEGER WILL USED BE FOR DATA TYPES OF NUMBERS
VARCHAR -- COMBINATION NUMERIC AND STRINGS
DATE -- INPUTS THE DATE FORMAT DD/MM/YYYY OR YYYY/MM/DD
DATETIME -- YYYY/MM/DD HH:mm:SS
CREATE DATABASE TEST
-- SELECT THE DATABASE
USE TEST
-- CREATE TABLES IS USED TO CREATE A METADATA OR SCHEMA OF TABLE
--SYNTAX CREATE TABLE <TABLE NAME> (COLNAME1 DATATYPE,COLNAME2 DATATYPE, COLNAME3
DATATYPE)
CREATE TABLE EMPLOYEE3 (ID INT, FIRST_NAME VARCHAR(20))
-- ADD A NEW COLUMN PHONE NUMBER
ALTER TABLE EMPLOYEE
ADD ADDRESS2 VARCHAR(20)
SELECT * FROM EMPLOYEE
SELECT FIRST_NAME as [FIRST NAME] FROM EMPLOYEE3
--DROP A COLUMN NAME
ALTER TABLE EMPLOYEE
DROP COLUMN ZIPCODE
INSERT INTO EMPLOYEE (ID, NAME, PHONE, ADDRESS, ADDRESS2)
VALUES (1, 'A',123456,'123 STREET','123 LANE')
INSERT INTO EMPLOYEE (ID, NAME, PHONE, ADDRESS, ADDRESS2)
VALUES (2, 'B',123456,'123 STREET','123 LANE')
INSERT INTO EMPLOYEE (ID, NAME, PHONE, ADDRESS, ADDRESS2)
VALUES (2, 'B',123456,'123 STREET','123 LANE')
INSERT INTO EMPLOYEE (ID, NAME, PHONE, ADDRESS, ADDRESS2)
VALUES (2, 'B',123456,'123 STREET','123 LANE')
SELECT * FROM EMPLOYEE
-- MULTI ROW INSERT
INSERT INTO EMPLOYEE (ID, NAME, PHONE, ADDRESS, ADDRESS2)
VALUES
(3, 'C',123456,'123 STREET','123 LANE'),
(4, 'D',123456,'123 STREET','123 LANE'),
(5, 'E',123456,'123 STREET','123 LANE'),
(6, 'F',123456,'123 STREET','123 LANE')
INSERT INTO EMPLOYEE (ID, NAME)
VALUES
(7,'G'),
(8,'H')
SELECT * FROM EMPLOYEE
CREATE TABLE EMPLOYEE6 (ID INT, NAME VARCHAR(20))
INSERT INTO EMPLOYEE6 (ID, NAME)
VALUES
(7,'G'),
(8,'H')
ALTER TABLE EMPLOYEE6
ADD PHONE INT
SELECT * FROM EMPLOYEE6
SQL CONSTRAINTS
NOT NULL-->Ensure column cannot have a null record
UNIQUE-->Ensure column does not have same values
PRIMARY KEY --> A combination of Not null and unique. Ensure that each row value is
unique and not a null value
Foreign key --> Prevents actions that would destroy links between tables
Check --> Ensures that rule which is setup is fulfilled while inserting records
DML - Data manupulation language
INSERT
UPDATE
DELETE
SYNTAX FOR INSERT
INSERT INTO <TABLENAME> (COL1,COL2,COL3) VALUES (COL1VALUE,COL2VAL,COL3VAL)
INSERT INTO <TABLENAME> VALUES (COL1VALUE,COL2VAL,COL3VAL)
DELETE VS TRUNCATE VS DROP
DELETE WILL ONLY REMOVE SPECIFIC ROWS BASED ON CONDITION
TRUNCATE WILL REMOVE ALL ROWS BUT KEEPS THE TABLE SCHEMA
DROP WILL REMOVE ALL ROWS AND TABLE SCHEMA AS WELL.