DDL: Data defination Language
create create tabel table_name(column_name data_type(size))
alter alter table add,modify,drop
delete delete table table_name
drop drop table table_name || drop view view_naem
rename rename table tabele_name to new_table_name
turncte truncate table table_name (removed and saved in hd:: no roll back)
DML: Data Manipulation Language
insert insert into table_name(values...)
update update table set old_value=new_value
delete delete from table_name(can roll back)
merge
DQL: Data Query Language
select select * from table_name
TCL: Transcation Control Language
rolback
commit
DCL: Data Control Language
grant
revoke
Constrains in Mysql:
1.Primary Key :
column level: [ Create table table_name( column_name datatype(size) primary key )]
table_level(also called as composite key)
[Create table table_name(columns_names...,primary key(column_names,..)]
2.Foreign Key
colum_level: [Create table tablename (column_name datatype(size) references table_name(column_name)]
table_level: [Create table table_name(columns..., foreign key(col1,col2) references table_name(column_name)]
table_level:
3.Not null only applyed on colum level
4.Unique: can have null but no duplicates
5.Check
Operators in Msql:
1.Arthemetic Operators : [+, - ,/, *, mod(a,b)]
2.Relational Operators: [=, >, <, !=]
3.Logical Operators: [and, or, not]
4.Special Operators: [in | not in, between | not between, not null | is not null, like | not like]
Functions in Mysql:
1.Number Functions: [abs(), mod(m,n), round(column), ceil(column), floor(column)]
2.Character Functions: [ ]
3.Date Functions
Group Functions in mysql:
1.max()
2.min()
3.avg()
4.sum()
5.count()
Joins:
1.Inner Join [ Return rows when there is match two tables]
2.Left Join / Left outer Join [Return all rows from left table]
3.Right Join / Right outer Join [Return all rows from right table]
4.Self Join [Used to join table it self. Must use alias for at least one table]
Mysql Union
1.union: select without duplicates
[Select * from table1
union
select * from table2
]
2.union all :with duplicates
[Select * from table1
union all
select * from table2
]
Mysql query Examples:
1.select first 3 characters of column (mysql position starts from 1. in oracle and sql it starts form 0
> select substring(columnname,1,5) from emp
2.get position of a character in column
>slect locate('charachter',column name) from emp
3.get lenght of the column character
>SELECT LENGTH(NAME) FROM emp
4.Get First_Name from employee table after replacing 'o' with '$'
create create tabel table_name(column_name data_type(size))
alter alter table add,modify,drop
delete delete table table_name
drop drop table table_name || drop view view_naem
rename rename table tabele_name to new_table_name
turncte truncate table table_name (removed and saved in hd:: no roll back)
DML: Data Manipulation Language
insert insert into table_name(values...)
update update table set old_value=new_value
delete delete from table_name(can roll back)
merge
DQL: Data Query Language
select select * from table_name
TCL: Transcation Control Language
rolback
commit
DCL: Data Control Language
grant
revoke
Constrains in Mysql:
1.Primary Key :
column level: [ Create table table_name( column_name datatype(size) primary key )]
table_level(also called as composite key)
[Create table table_name(columns_names...,primary key(column_names,..)]
2.Foreign Key
colum_level: [Create table tablename (column_name datatype(size) references table_name(column_name)]
table_level: [Create table table_name(columns..., foreign key(col1,col2) references table_name(column_name)]
table_level:
3.Not null only applyed on colum level
4.Unique: can have null but no duplicates
5.Check
Operators in Msql:
1.Arthemetic Operators : [+, - ,/, *, mod(a,b)]
2.Relational Operators: [=, >, <, !=]
3.Logical Operators: [and, or, not]
4.Special Operators: [in | not in, between | not between, not null | is not null, like | not like]
Functions in Mysql:
1.Number Functions: [abs(), mod(m,n), round(column), ceil(column), floor(column)]
2.Character Functions: [ ]
3.Date Functions
Group Functions in mysql:
1.max()
2.min()
3.avg()
4.sum()
5.count()
Joins:
1.Inner Join [ Return rows when there is match two tables]
2.Left Join / Left outer Join [Return all rows from left table]
3.Right Join / Right outer Join [Return all rows from right table]
4.Self Join [Used to join table it self. Must use alias for at least one table]
Mysql Union
1.union: select without duplicates
[Select * from table1
union
select * from table2
]
2.union all :with duplicates
[Select * from table1
union all
select * from table2
]
Mysql query Examples:
1.select first 3 characters of column (mysql position starts from 1. in oracle and sql it starts form 0
> select substring(columnname,1,5) from emp
2.get position of a character in column
>slect locate('charachter',column name) from emp
3.get lenght of the column character
>SELECT LENGTH(NAME) FROM emp
4.Get First_Name from employee table after replacing 'o' with '$'
>select REPLACE(FIRST_NAME,'o','$') from employee
5.select name and first name in single column
>SELECT CONCAT(id,NAME) FROM emp
6.Select year month and date from a table
>select year(date), date(date), month(date)
7.select employee whose joing date is 2013
>select * from emp where year(joiningdate)='2013'
8.get joining date and time
>slect * from convert(date_format(joining_date,'yyyy-mm-dd hh:mm:ss:ff' ff= milliseconds
9.get database date
>slect now()
"Top N Salary" SQL Interview Questions and Answers
10.Select TOP 2 salary from employee table
>select * from employee order by salary desc limit 2
11.Select TOP N salary from employee table
>select * from employee order by salary desc limit N
12.Select 2nd Highest salary from employee table
>select min(SALARY) from (select * from employee order by salary desc limit 2)
13.Select Nth Highest salary from employee table
>select min(SALARY) from (select * from employee order by salary desc limit N)
Views:
View is a database object which is used to provide authority level of Security. Generally views doesnot store any data
view are 2 types
1.single view
2.complex view
ex: CREATE VIEW v1 AS SELECT * FROM emp
>how to load data into table using mysql
load data loacal infile = "c:\\desktop\\file.txt"
into table table_name
fields terminated by ","
lines terminated by "\n"
(field1,field2....)
local is used if we trying to insert from user to remote system
>difference between union and join.
union place the records in one column of two table records one after another
example
table 1:
a
b
c
table 2:
d
e
f
union:
select colum_name from table_1
union
select colum_name from table_2
result: will be placed in single column
a
b
c
d
e
f
>types of indexs:
index is one of the essintial feature of the mysql wi
indexes store data in logcally sorted manner to improve search performance.
indexes decrease write performance
Views:
View is a database object which is used to provide authority level of Security. Generally views doesnot store any data
view are 2 types
1.single view
2.complex view
ex: CREATE VIEW v1 AS SELECT * FROM emp
>how to load data into table using mysql
load data loacal infile = "c:\\desktop\\file.txt"
into table table_name
fields terminated by ","
lines terminated by "\n"
(field1,field2....)
local is used if we trying to insert from user to remote system
>difference between union and join.
union place the records in one column of two table records one after another
example
table 1:
a
b
c
table 2:
d
e
f
union:
select colum_name from table_1
union
select colum_name from table_2
result: will be placed in single column
a
b
c
d
e
f
>types of indexs:
index is one of the essintial feature of the mysql wi
indexes store data in logcally sorted manner to improve search performance.
indexes decrease write performance
