SQL commands
To Create a Database
Create Database School;
To change Database
Use Database_Name;
To Show Database
Show Databases;
To Drop a Database
Drop database Database_Name;
To Create a Table in Database
CREATE TABLE Teacher
(
Teacher_ID INTEGER,
First_Name VARCHAR(20),
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2),
Date_of_Birth DATE,
Dept_No INTEGER
);
To show Tables
Show Tables;
To Drop a Table in Database
Drop Table Table_Name;
To Rename a Tablename in Dataabase
Alter Table teacher Rename Student;
To insert Rows or Data in Table
INSERT INTO Teacher
VALUES (101,"Shanaya", "Batra", 'F', 50000, '1984-08-11', 1);
To delete a Single Row in Table
Delete from Table_Name
Where teacher_id=101;
To Display Only Columns in Table
SHOW COLUMNS FROM table_name;
To Update data in any cell of Particular Row
Update Table_Name
Set Salary=50000
Where Teacher_ID=101;
To display All data in Table
Select * from Table_Name
To particular Display data of Column in Table
Select First_Name from Table_Name;
To Display only data which salary >50000
Select First_Name from Table_Name
Where salary>50000;
To Delete all rows in Table
Delete from teacher;
Adding a Column in Table
ALTER TABLE Teacher ADD Age INTEGER;
To Drop a column in Table
ALTER TABLE Teacher DROP Dept_No ;
To Alter a Column in Table
ALTER TABLE Teacher ALTER Salary DROP DEFAULT;
ALTER TABLE Teacher ALTER Salary SET DEFAULT 30000;
To Rename a Column name in Table
Alter table teacher rename column First_Name to FirstName;
How to Change or Add Data type in Particular column in Table
ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;
Exp. (ALTER TABLE employees MODIFY COLUMN age INT;)
To Add a Primary Key in Table
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
Query: To list the names of all the Departments in the Ascending
order of their names.
Select * from Table_Name
Order By First_Name;
Query: To list the names of all the Departments in the descending
order of their names.
SELECT Dept_Name
FROM Department
ORDER BY Dept_Name DESC;
Queries in SQL
SELECT Teacher_ID,First_Name,Last_Name, Dept_No
FROM Teacher
WHERE Dept_No = 4 OR Dept_No = 7;
SELECT First_Name, Last_Name, Dept_ID, Dept_Name FROM Teacher,
Department;
SELECT First_Name, Last_Name, Dept_ID, Dept_Name
FROM Teacher, Department WHERE Dept_ID=Dept_No;
Query: To retrieve names of all the teachers starting from letter
'S'.
SELECT First_Name
FROM Teacher
WHERE First_Name LIKE "S%";
Query: To retrieve names of all the teachers having 6 characters in the
first name and starting with 'S'.
SELECT First_Name
FROM Teacher
WHERE First_Name LIKE "S_ _ _ _ _";
Query: T o retrieve all the details of those employees whose last
name is not specified.
SELECT * FROM Teacher
WHERE Last_Name IS NULL;
Query: T o find total salary of all the teachers .
SELECT SUM(Salary) AS Total_Salary FROM Teacher;
Query: T o find the maximum and minimum salary .
SELECT MAX(Salary) AS Max_Salary, MIN(Salary) AS
Min_Salary FROM Teacher;
To Add/Edit Primary Key of Particular Column Name in Table
Alter table teacher modify column Teacher_ID integer Primary Key;
