SQL All

It seems foreign key constraint can be added if it's primary key on both the tables, I goota verify this thing.

Important terms:
Candidate key - minimal superkey
Prime and non prime attribute -
Super key  - identify unique tuples
Unique - all values in column unique , can be combination also (0 ?)
simple key - single unique key
compound key - unique(single) key which is combination of >1
composite key - compound + 1 ( ? , wiki is confusing )
Primary - select candidate key
Functional dependency - one determines another uniquely

1NF - Atomic key
2NF - Whole(primary) key
3NF - Only (primary)key
BCNF - For every fd X->Y , X must be superkey

Normalization - reduce data redundancy , prevent insert,update,delete anomalies

Advanced Topics :
Multivalued dependency -
Join dependency
4NF , 5NF , 6NF

**********************************************************************
High
How to design database schema?
Design ecommerce database.
What is normalization? What's the benefits? What are different levels of normalization?
Are foreign keys indexed by default?
Surrogate vs natural primary keys?
Best practices?
What are joins? Different types of joins?

Avg:

Low:








 *********************All SQL Commands*********************************

/*
SQL is a standard language for accessing and manipulating relational databases.
SQL stands for Structured Query Language.
SQL is not case sensitive. Standard is to write keywords in capital letters.
; is used to separate multiple SQL statements.
*/

show databases;

create database mydb;

drop database mydb;

use mydb;

show tables;

create table employee(
 empId int PRIMARY KEY,
 empName varchar(30) NOT NULL,
 manId int
);

desc employee;

insert into employee values(1,'Veni',null);

insert into employee(empId,empName) values(1,'Veni');

insert into employee(empId,empName,manId) values(2,'Nitin',1);

select * from employee;

select empId from employee;

select empId,empName from employee;

delete from employee where empId=1;

delete from employee;

select distinct empName from employee;

/*Following will show duplicate names if id are different, whole row should be unique*/
select distinct empId,empName from employee;

/*Following will not work*/
/*select empId,distinct empName from employee;*/

select * from employee where empId>1;

select empId,empName from employee where empId>1;

select * from employee where empName='Praveen';

select * from employee where empId>3 and empName='Praveen';
select * from employee where empId>3 && empName='Praveen';

select * from employee where empId=3 or empName='Nitin';
select * from employee where empId=3 || empName='Nitin';

select * from employee order by empId;
select * from employee order by empId desc;

update  employee set manId=1 where empId=4;

select * from employee order by empName, manId;

select * from employee order by empId,empName,manId;

select * from employee order by empName desc,manId asc;

update employee set empName='Pravin',manId=1 where empId=3;

/*Following will not work in MySQL*/
/*delete * from employee;*/

select * from employee where empName='pk' or 1=1; /*SQL Injection*/

select * from employee limit 2;

select * from employee limit 2,4; /*will print record 3,4*/

select * from employee where empName like 'Pr%';

select * from employee where empName like 'V_n_';

/*Following will not work */
/*select * from employee where empName like '[VP]%';*/
/*To achieve same use regexp or rlike*/
select * from employee where empName regexp '[VP](.)*';
select * from employee where empName rlike '[VP](.)*';

select * from employee where empName in('Nitin','Praveen');

select * from employee where empId in(1,2);

select * from employee where empId not in(1,2);

select * from employee where empId between 2 and 4;/*will output 2,3,4*/

select * from employee where empId not between 2 and 4;/*will output except 2,3,4*/

select * from employee where empName between 'a' and 'p';

update employee set join_date='2013-04-15' where empId=4;/*date format must be 'YYYY-MM-DD' */

select * from employee where join_date between '2013-04-01' and '2013-04-30';

select empName as 'Employee Name' from employee; /*Alias*/

select concat(empId,',',empName) as employee from employee;

select e.empId from employee e; /* Table alias */

drop table employee;

truncate table employee;
/*Joins*/
select e.empId,e.empName,a.houseNum from employee e,address a where e.empId=a.empId; /*join*/

select e.empId,e.empName,a.houseNum from employee e,address a where
e.empId=a.empId and e.empId=1;

select e.empName,a.houseName from
employee e join address a
on e.empId=a.empId; /*inner join*/

select e.empName,a.houseName from
employee e left join address a
on e.empId=a.empId;

select e.empName,a.houseName from
employee e left outer join address a
on e.empId=a.empId;

select e.empName,a.houseName from
employee e right join address a
on e.empId=a.empId;
select e.empName,a.houseName from
employee e right outer join address a
on e.empId=a.empId;

/*MySQL does not support full join */

select empId from employee
union
select empId from address;

select empId from employee
union all
select empId from address;

select empId from employee where empId>2
union all
select empId from address where empId>3;

/*Schema of employee_backup must be same as employee*/
insert into employee_backup select * from employee;
/*select into does not work in mysql */

/*MySQL constraints*/
create table Person(
Id int Auto_Increment PRIMARY KEY,/*serial does not work*/
Name varchar(30) NOT NULL,
EmailId varchar(30) UNIQUE NOT NULL,
Age int check(Age>0), /*Will not work,In mysql check is parsed but ignored */
insert_time datetime DEFAULT now()
);
create table Person_Address(
Id int primary key,
HouseName varchar(30),
foreign key (Id) references Person(Id)
on delete cascade
);

/*Indexes*/
create index pin on person(emailid);
show indexes from person;
alter table person drop index pin;

alter table person add dob date;
alter table person modify dob datetime;
alter table person drop column dob;

/*Views*/
/*View is a virtual table based on the result set of an sql statement.*/
create view myview as select * from person;
create view myview as select * from person;
/*View gets automatically updated*/
create or replace view myview as select * from person where id>1;

/*To see all tables*/
show full tables in mydb;
show full tables;
show tables;
/*To see all views in db */
show full tables in mydb where table_type='View';
show full tables where table_type='View';

drop view myview;

/* Date */
select now();/*gives current date time*/
select sysdate();
select curdate();/*gives current date*/
select current_date();
select curtime();/*gives current time*/
select current_timestamp;

select date(insert_time) from person;/*extract date*/

/*extract everything from datetime*/
select extract(year from insert_time) as year,
extract(month from insert_time) as month,
extract(day from insert_time) as day,
extract(hour from insert_time) as hour,
extract(minute from insert_time) as minute,
extract(second from insert_time) as second,
extract(microsecond from insert_time) as microsecond
from Person where id=1;

/*Add date*/
/*possible values for interval are: day,year,month,hour,minute,second,microsecond,
week,quarter,etc.*/
select insert_time, date_add(insert_time,interval 30 day)
as renewal_date from person;
select insert_time, date_add(insert_time,interval 30 year)
as renewal_date from person;

select insert_time, date_sub(insert_time,interval 30 day)
as renewal_date from person;
select insert_time, date_sub(insert_time,interval 30 year)
as renewal_date from person;

/*curdate() does not work as default value*/
/*4 types of date format in mysql*/
create table mydate(
id int,
date date,
datetime datetime default now(),
timestamp timestamp default current_timestamp,
year year
);

select date_format(now(),'%d %b %y');/*a lot of options here*/

select datediff('2016-02-04','2016-02-08');/*output= -4 */

select * from person where insert_time > 2016-02-03;

select * from person where age is null;
select * from person where age is not null;

/*Following will return null if age is null*/
select id+age as total from person;
/*Following will set age to 0 if age is null*/
select id+ifnull(age,0) as total from person;
/*Another way is to use coalesce*/
select id+coalesce(age,0) as total from person;

/*Data types*/
create table MySQLDataTypes(
 charCol char(10),
 varcharCol varchar(30),
 textCol text,
 blobCol blob,
 enumCol enum('0','1'),
 setCol set('eena','meena','deeka'),
 intCol int,
 bigIntCol bigint,
 floatCol float,
 doubleCol double
);
/*set same as enum,can hold more values and more than one 
at same time */

/* Aggregate Functions */
select avg(salary) from employee;
select count(*) from employee;
select count(salary) from employee;
select sum(salary) from employee;
select max(salary) from employee;
select min(salary) from employee;
select count(distinct empName) from employee;
/*Following will not work*/
/*select count(distinct *) from employee; */

/*Scalar functins */
select ucase(empName) from employee;
select upper(empName) from employee;
select lcase(empName) from employee;
select lower(empName) from employee;

/*For Veni , below will return Ve */
select mid(empName,1,2) from employee;
select substr(empName,1,2) from employee;
select mid(empName,1) from employee;
select substr(empName,1) from employee;

/*Will return result till 1 decimal places, will round not truncate*/
select round(salary,1) from employee;
select format(salary,1) from employee;

select length(empname) from employee;
select char_length(empName) from employee;

select concat(empName,salary) from employee;

/*Not case sensitive, P% same as p%*/
select * from employee where empName like 'P%';
select * from employee where empName not like 'P%';
select * from employee where empName regexp 'P(.)*';
select * from employee where empName rlike 'P(.)*';
select * from employee where empName not regexp 'P(.)*';

select reverse(empname) from employee;

select trim("  hello   ");

select empname,count(empname) from employee group by empname;
select empname,count(empname) from employee group by empname
having count(empName)>1;

Hello