SQL Commands
Lecturer No 6
To see existing databases
show databases;
To create new database:
create database database_name;
To use existing databases;
use database_name;
To creating table
create table table_name (
column1 datatype[size],
column2 datatype[size],
column3 datatype [size],
....
);
To see existing tables
show tables;
creating new table from existing table
create table table_name as select * from existing table_name;
creating new table having specific fields but all records from existing
table.
create table table_name as select field1,field2 from existing
table_name;
creating new table having specific records but all fields from existing
table.
create table table_name as select * from existing table_name
where condition;
creating new table having no records but all fields from existing
table.
create table table_name as select * from existing table_name
where false condition;
To insert records into the table
Insert into table_name values (vale1,value2….);
Lecturer No 7
Alter Table
Alter table query is used to modify structure of a table. We can add
delete modify column.
[Link] New Column in a Table
alter table table_name add column_name datatype;
[Link] Column from Table
alter table table_name drop column column_name;
[Link] Column of a Table
alter table table_name modify column column_name datatype;
[Link] of Table
alter table table_name rename to new_name;
Truncate Table
This command is used to delete all records from table.
truncate table table_name;
Drop Table
Drop table command is used to delete table permanently from the
database.
drop table table_name;
Desc
Desc query is used to display structure of table
desc table_name;
Lecturer No 8
Primary Key
create table emp (eno int ,primary key(eno));
Foreign Key
create table Course_Details (course_id int,course_code
varchar(20)); basic table
create table student2020(stud_id int,name
varchar(20),course_code varchar(20) references
Course_Details(course_code)); reference table
Unique Key
create table student20(stud_id int,name
varchar(20),course_code varchar(20)unique);
Lecturer No 9
NOT NULL
create table Course (course_id int not null,course_code
varchar(20));
DEFAULT
create table stud(rno int,sname varchar(20),age int default 15);
CHECK
create table stud1(rno int,sname varchar(20),age int
check(age>=18));
Lecturer No 10
DML Commands
INSERT
[Link] values in all column
insert into table_name values (value1,value2,……);
[Link] values in specific column
insert into table_name (column1,column2…)values
(value1,value2,……);
[Link] records from exesting table into new table
insert into table_name select column1,column2… from
table_name2 where condition;
UPDATE
update table_name set column_name=new value[where
condition]
DELETE
[Link] Delete all records
delete from table_name;
[Link] Delete specific records
delete from table_name where condition;
Lecturer No 11
DQL Commands
SELECT
[Link] select column wise data
select column1, column2…from table_name;
[Link] select all data
select * from table_name;
WHERE CLAUSE
select column1, column2…from table_name where condition;
DISTINCT CLAUSE
select distinct (column_name) from table_name;
DCL Commands
GRANT
grant select insert update delete on table_name to user_name;
REVOKE
revoke select insert update delete on table_name from
user_name;
SAVEPOINT & ROLLACK
To create savepoint
savepoint savepoint_name;
To get rollback
rollback to savepoint_name;
COMMIT
commit;
Lecturer No 12
SQL Operators
Arithmetic +, -, *, /, %
Operators
=, >, <, >=, <=,
Comparison
Operators
!=, <> , !>, !<
ALL ,AND ,ANY, BETWEEN,
SQL Operators Logical Operators
EXISTS, IN, LIKE, NOT, OR, SOME
Bit wise & | ^
Operators
Compound += -= /= %=
Operators &= ^= |*=
Lecturer No 13
SET Operators
UNION
select column_name from table_1
union
select column_name from table_2;
select deptno from emp union select deptno from dept;
UNION ALL
select column_name from table_1
union all
select column_name from table_2;
INTERSECT
select column_name from table_1
intersect
select column_name from table_2;
MINUS
select column_name from table_1
minus
select column_name from table_2;
BETWEEN & LIKE
BETWEEN
select * from emp where sal between 25000 and 35000;
LIKE
select * from emp where name like "A%";
