SQL for Beginners Series - Session 8

UPDATE | DELETE | ALTER | DROP in SQL

--************** Session 7 Notes

--> 1. Creation of database using SQL

------------------------------------------

--Syntax: 

CREATE DATABASE <Database Name>

--> Creating database "SQL_Demo" Using SQL

CREATE DATABASE SQL_Demo

--> 2. Creation of Table in SQL (DDL)

------------------------------------------

--Syntax: 

CREATE TABLE  <Table Name> ( 

Col1 <COlumnDatatype> <Nullability>,

Col2 <COlumnDatatype> <Nullability>,

Col3 <COlumnDatatype> <Nullability>,

Col4 <COlumnDatatype> <Nullability>,

Col5 <COlumnDatatype> <Nullability>)

USE SQL_Demo

-- tblEmployee

CREATE TABLE tblEmployee ( -- Drop table tblEmployee

Empid INT NOT NULL,

EmpName Varchar(20) NULL,

EmpLocation Varchar(20),

DOJ DATE,

Salary INT)

--> NULL is the deafult Nullability

--> 3. Inserting data into the Table in SQL (DML)

--------------------------------------------------

--Syntax: 

INSERT INTO <TableName> (col1, col2, col3, col4) Values (col1, col2, col3, col4)

INSERT INTO tblEmployee (Empid,EmpName,EmpLocation,DOJ,Salary) 

Values (1,'Smith','India', '2020-01-02',20000)

INSERT INTO tblEmployee (Empid,EmpName,EmpLocation,DOJ,Salary) 

Values (2,'George Mark','US', '2019-04-28',50000)

INSERT INTO tblEmployee (Empid,EmpName,EmpLocation,DOJ,Salary) 

Values (3,'Emp3','UK', '2018-04-28',25000),(4,'Emp4','UK', '2016-04-25',60000)

--> inserting data to specific columns

INSERT INTO tblEmployee (Empid,EmpName,DOJ) 

Values (5,'Emp5','2011-04-28')

INSERT INTO tblEmployee (Empid,EmpName,DOJ) 

Values (8,'Emp5','Emp3')

--> 4. Selecting data from the Table in SQL (DML)

--------------------------------------------------

--Syntax: 

SELECT * FROM <Table Name>

SELECT * FROM tblEmployee

-- * - All columns

SELECT * FROM tblEmployee

SELECT Empid,EmpLocation, Salary FROM tblEmployee

--************** Session 8 Starts from here***********************************

--> WHERE Clause in SQL --> Filter rows based on a specific condition

SELECT * FROM tblEmployee WHERE Empid = 3

--> UPDATE (DML)

SELECT * FROM tblEmployee

--syntax:

UPDATE <table>

SET Column1_tgt = <value1>

WHERE <Condtion> --> updates all rows of a table if you are ignoring the where clause

Run a SELECT Statement before running the UPDATE to see how many rows are getting impacted

Here, we are updating the location of Employee 1 from India to US

SELECT * FROM tblEmployee

UPDATE tblEmployee

SET EmpLocation = 'US'

WHERE Empid = 1

SELECT * FROM tblEmployee

--> DELETE (DML)

DELETE FROM <table> WHERE <Condtion>

-- WHERE clause is important .. if the delete is run on table without a where clause would delete all rows 

--from a table

Run a SELECT Statement before running the UPDATE to see how many rows are getting impacted

SELECT * FROM tblEmployee WHERE Empid = 1

SELECT * FROM tblEmployee WHERE Empid = 1

DELETE FROM tblEmployee WHERE Empid = 1

SELECT * FROM tblEmployee WHERE Empid = 1

-- ALTER (DDL)

-- Empid from INT to BIGINT

SELECT * FROM tblEmployee

--> Altering an existing column

ALTER TABLE table_name 

ALTER COLUMN column_name new_data_type(size);

ALTER TABLE tblEmployee

ALTER COLUMN Empid BIGINT 

--> Addition of a new column

ALTER TABLE table_name

ADD column_name data_type column_constraint;

ALTER TABLE tblEmployee

ADD Deptid INT NULL

SELECT * FROM tblEmployee

-- DROP 

--> remove a table or object from the database

--syntax:

DROP <object> <objectname>

DROP TABLE tblEmployee