SQL QUERIES for Recall
CREATE TABLE employees (emp_id INT ,first_name VARCHAR(50)
,email VARCHAR(50) ,company_name VARCHAR(50) ,salary
DECIMAL(7, 2));
INSERT INTO employees (first_name, email, company_name,
salary)VALUES ('iron mam', 'ironman@mail', 'Oracle',
30000.00),('captain america', 'cap@gmail.com', 'Oracle',
35000.00),('hulk', 'green@mail', 'Google', 40000.00),('spidy',
'spider@mail', 'Microsoft', 45000.00),('batman', 'black@mail',
'Microsoft', 45000.00),('superman','super@mail','world','50000');
How to select data from table:
SELECT * FROM employees;
SELECT name, position FROM employees;
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE position = 'Manager' AND salary
> 50000;
SELECT name, position
FROM employees
WHERE (position = 'Manager' OR position = 'Developer') AND salary
> 60000
ORDER BY name ASC
LIMIT 10;
SELECT * FROM employees WHERE salary is not NULL;
UPDATE and DELETE FROM a TABLE:
UPDATE employees
SET salary = 70000
UPDATE employees
SET salary = 70000
WHERE id = 1;
UPDATE employees
SET position = 'Senior Developer'
WHERE position = 'Developer';
UPDATE employees
SET salary = salary * 1.10
WHERE position = 'Manager';
DELETE FROM employees
WHERE salary < 30000;
Date and time
Create table test(my_date date,my_time time,My_dateTime
datetime);
Insert into table values(current_date(),current_time(),now());
Current_date() – 1;
Drop table test;
CREATE TABLE employees (emp_id INT NOT NULL
AUTO_INCREMENT,first_name VARCHAR(50) NOT NULL,email
VARCHAR(50) UNIQUE,company_name VARCHAR(50) DEFAULT
'oracle',salary DECIMAL(7, 2) CHECK (salary > 25000),PRIMARY
KEY (emp_id));
INSERT INTO employees (first_name, email, company_name,
salary)VALUES ('iron mam', 'ironman@mail', 'Oracle',
30000.00),('captain america', 'cap@gmail.com', 'Oracle',
35000.00),('hulk', 'robert.brown@example.com', 'Google',
40000.00),('spidy', 'emily.vis@example.com', 'Microsoft',
45000.00),('batman', 'emily.davis@exae.com', 'Microsoft',
45000.00),('superman','super@mail','world','50000');
Primary key:
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
position VARCHAR(50),
salary DECIMAL(10, 2),
PRIMARY KEY (id)
);
Foreign key:
CREATE TABLE departments (
dept_id INT NOT NULL AUTO_INCREMENT,
dept_name VARCHAR(100) NOT NULL,
PRIMARY KEY (dept_id)
);
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
position VARCHAR(50),
salary DECIMAL(10, 2),
dept_id INT,
PRIMARY KEY (id),
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
Unique constraint:
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT,
email VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
position VARCHAR(50),
salary DECIMAL(10, 2),
PRIMARY KEY (id),
UNIQUE (email)
);
Not NULL:
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
position VARCHAR(50),
salary DECIMAL(10, 2),
PRIMARY KEY (id)
);
Default:
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
position VARCHAR(50) DEFAULT 'Employee',
salary DECIMAL(10, 2),
PRIMARY KEY (id)
);
Check Constraint:
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
position VARCHAR(50),
salary DECIMAL(10, 2) CHECK (salary > 0),
PRIMARY KEY (id)
);
Alter command:
Add a column
ALTER TABLE table_name
ADD column_name column_type;
ALTER TABLE table_name
ADD column_name column_type after Counm_name
;
Drop colunm:
ALTER TABLE employees
DROP COLUMN birthdate;
Modyfy column type:
ALTER TABLE employees
MODIFY salary DECIMAL(12, 2);
Renaming a clounm:
ALTER TABLE table_name
CHANGE old_column_name new_column_name column_type;
CREATE TABLE employees (emp_id INT ,first_name VARCHAR(50)
,email VARCHAR(50) ,company_name VARCHAR(50) ,salary
DECIMAL(7, 2),pincode int);
Alter command for constraints:
Adding primary key:
ALTER TABLE employees
ADD PRIMARY KEY (id);
Foreign key:
alter table weapon add primary key(wp_id);
alter table heros add constraint fk_id foreign key(wp_id) references
weapon(wp_id);
alter table heros drop foreign key fk
Add foreign key;
ALTER TABLE employees ADD CONSTRAINT fk_department
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);
Unique:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);
Check constraint:
ALTER TABLE employees
ADD CONSTRAINT check_salary CHECK (salary >= 0);
Drop primary key:
ALTER TABLE employees
DROP PRIMARY KEY;
Drop foreign key:
ALTER TABLE employees
DROP FOREIGN KEY fk_department;
Basic Functions:
Count
Sum
Max
Min
Avg
Concat
Logical operator:
And:
SELECT * FROM table_name
WHERE condition1 AND condition2;
OR:
SELECT * FROM table_name
WHERE condition1 OR condition2;
Not:
SELECT * FROM table_name
WHERE NOT condition;
In:
SELECT * FROM table_name
WHERE column_name IN (value1, value2, ...);
Between:
SELECT * FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Wild card characters:
%:
SELECT * FROM table_name
WHERE column_name LIKE 'abc%';
_
SELECT * FROM table_name
WHERE column_name LIKE '_bc';
Character set [];
SELECT * FROM table_name
WHERE column_name LIKE '[a-c]%';
Negation of character set:
SELECT * FROM table_name
WHERE column_name LIKE '[!a-c]%';
Range of characters:
SELECT * FROM table_name
WHERE column_name LIKE 'a[c-f]d';
Order by:
Select * from employees order by amount asc;
Select * from employees order by amount desc;
Select * from employees order by amount,first_name;
Limit clause:
It limit the records that are querried:
Select * from employees limit 3;
Select * from employees order by name limit 3;
Select * from employees limit 3, 6; ------------It will take the cursor
to the 3 rd one and from there it will limit 6 rows;
For Join:
CREATE TABLE heros (emp_id INT,first_name VARCHAR(50) ,email
VARCHAR(50) ,company_name VARCHAR(50) ,salary DECIMAL(7,
2),wp_id int);
create table weapon(wp_id int,Wep varchar(50));
insert into weapon
values(1,'GUNS'),(2,'Shield'),(3,'Bow'),(4,'Power');
JOIN
select heros.first_name,heros.salary,weapon.wep from heros join
weapon on heros.wp_id = weapon.wp_id;
Inner join:
select heros.first_name,heros.salary,weapon.wep from heros inner
join weapon on heros.wp_id = weapon.wp_id;
Table short form:
select h.first_name,h.salary,w.wep from heros as h join weapon as w
on h.wp_id = w.wp_id;
Alias name:
select h.first_name as hero,h.salary,w.wep as power from heros as h
join weapon as w on h.wp_id = w.wp_id order by h.first_name;
Inner join using where:
select h.first_name as hero,h.salary,w.wep as power from heros as
h,weapon as w where h.wp_id = w.wp_id;
Cross Join :
join to find the count of heros using that power;
Select w.wep,count(h.wp_id) from weapon as w join heros as h on
h.wp_id=w.wp_id group by w.wep;
Example:
select sum(salary) from heros where salary >15000;
select sum(salary) from heros where salary >15000 group by wp_id;
Unions:
Need same no of colunms and and should be of same data type
Union
Then
Union all
1. DDL – Data Definition Language
2. DQL – Data Query Language
3. DML – Data Manipulation Language
4. DCL – Data Control Language
5. TCL – Transaction Control Language
DDL:
Create
Alter
Drop
Truncate: -delete the values not table:
Truncate table table_name;
DML:
Insert
Update
Delete
DQL:
Select
TCL:
Autocommit
Commit
rollback
set autocommit = off;
commit;
rollback;
DCL :
Grant and revoke:
Grant all on Table_name to User_name@ Host_name;
Revoke all on Table_name from User_name@Host_name;
Previleges:
SELECT
INSERT
DELETE
UPDATE
ALL
