SQL All Notes

SQL PROFILER -   https://vasanthrv.blogspot.com/2022/08/sql-profiler.html


CREATE TABLE Test (

Id int PRIMARY KEY IDENTITY(1,1) NOT NULL,

Name varchar(30) NOT NULL,

City varchar(50) NULL,

 )

Insert into Test(Name,City) values('',  '')

16:21

update Test set Name = 'Kumar' where id=3

delete from Test Where id=3

 CREATE TABLE regions (


region_id INT IDENTITY(1,1) PRIMARY KEY,

region_name VARCHAR (25) DEFAULT NULL


);


DEFAULTconstraint

CREATE TABLE countries (

country_id CHAR (2) PRIMARY KEY,

country_name VARCHAR (40) DEFAULT NULL,

region_id INT  NOT NULL

);

FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE

16:39

 INSERT INTO regions(region_name) VALUES ('Asia');

 INSERT INTO regions(region_name) VALUES ('Europe');


INSERT INTO countries(country_id,country_name,region_id) VALUES ('AR','Argentina',2);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('AU','Australia',3);


 1h 14m 28s

 53m 22s


------------------

CREATE TABLE users (

    user_id INT Identity(1,1) PRIMARY KEY,

    username VARCHAR(255) NOT NULL,

    password VARCHAR(255) NOT NULL,

    CONSTRAINT uc_username UNIQUE (username)

);

insert into users values('test1','ttt')


insert into users values('test1','ttt')

CREATE TABLE trainings (

    employee_id INT Identity(1,1) PRIMARY KEY,

    course_id INT,

    taken_date DATE NOT NULL,

    );

INSERT INTO trainings(employee_id,course_id)

VALUES(1,1); ---error


INSERT INTO trainings(employee_id,course_id,taken_date)

VALUES(1,1,GETDATE());



CREATE TABLE products (

    product_id INT PRIMARY KEY,

    product_name VARCHAR(255) NOT NULL,

    selling_price int CHECK (selling_price > 0)

);


insert into products values(1,'test',0)


insert into products values(1,'test',50)

CREATE TABLE Orders (

    ID int NOT NULL,

    OrderNumber int NOT NULL,

    OrderDate date DEFAULT GETDATE()

);



insert into Orders(ID,OrderNumber ) Values(1,1)

SELECT REVERSE('abcd') 

SELECT UPPER('Allah-hus-samad');

select UPPER(first_name) from employees

 SELECT  ROUND(157.96,0) 

SELECT GETDATE()

SELECT  DAY(GETDATE())

SELECT FORMAT (getdate(), 'dd-MM-yyyy') as date

Date Parts: can use the name or listed abbreviations:


year, yy, yyyy

quarter, qq, q

month, mm, m

dayofyear, dy, y*

day, dd, d*

weekday, dw, w*

week, wk, ww

hour, hh

minute, mi, n

second, ss, s

millisecond, ms

microsecond, mcs

nanosecond, ns








select convert(varchar, getdate(), 1)






-----------------------

 CREATE TABLE regions (

region_id INT IDENTITY(1,1) PRIMARY KEY,


region_name VARCHAR (25) DEFAULT NULL

);

CREATE TABLE countries (


country_id CHAR (2) PRIMARY KEY,


country_name VARCHAR (40) DEFAULT NULL,


region_id INT  NOT NULL,


FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE


);

CREATE TABLE locations(


location_id INT IDENTITY(1,1) PRIMARY KEY,

street_address VARCHAR (40) DEFAULT NULL,

postal_code VARCHAR (12) DEFAULT NULL,

city VARCHAR (30) NOT NULL,


state_province VARCHAR (25) DEFAULT NULL,


country_id CHAR (2) NOT NULL,


FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE


);






CREATE TABLE jobs (


job_id INT IDENTITY(1,1) PRIMARY KEY,


job_title VARCHAR (35) NOT NULL,


min_salary DECIMAL (8, 2) DEFAULT NULL,


max_salary DECIMAL (8, 2) DEFAULT NULL


);








CREATE TABLE departments (


department_id INT IDENTITY(1,1) PRIMARY KEY,


department_name VARCHAR (30) NOT NULL,


location_id INT  DEFAULT NULL,


FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE


);






CREATE TABLE employees (


employee_id INT IDENTITY(1,1) PRIMARY KEY,


first_name VARCHAR (20) DEFAULT NULL,


last_name VARCHAR (25) NOT NULL,


email VARCHAR (100) NOT NULL,


phone_number VARCHAR (20) DEFAULT NULL,


hire_date DATE NOT NULL,


job_id INT  NOT NULL,


salary DECIMAL (8, 2) NOT NULL,

manager_id INT  DEFAULT NULL,

department_id INT  DEFAULT NULL,

FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,


);


INSERT INTO regions(region_name) VALUES ('Europe');

INSERT INTO regions(region_name) VALUES ('Americas');

INSERT INTO regions(region_name) VALUES ('Asia');

INSERT INTO regions(region_name) VALUES ('Middle East and Africa');


INSERT INTO countries(country_id,country_name,region_id) VALUES ('AR','Argentina',2);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('AU','Australia',3);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('BE','Belgium',1);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('BR','Brazil',2);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('CA','Canada',2);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('CH','Switzerland',1);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('CN','China',3);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('DE','Germany',1);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('DK','Denmark',1);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('EG','Egypt',4);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('FR','France',1);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('HK','HongKong',3);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('IL','Israel',4);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('IN','India',3);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('IT','Italy',1);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('JP','Japan',3);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('US','US',4);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('UK','UK',4);

INSERT INTO countries(country_id,country_name,region_id) VALUES ('DE','DE',4);



INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('2014 Jabberwocky Rd','26192','Southlake','Texas','US');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('2011 Interiors Blvd','99236','South San Francisco','California','US');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('2004 Charade Rd','98199','Seattle','Washington','US');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('2004 Charade Rd','98199','Seattle','Washington','US');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('8204 Arthur St',NULL,'London',NULL,'UK');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');

INSERT INTO locations(street_address,postal_code,city,state_province,country_id) VALUES ('Schwanthalerstr. 7031','80925','Munich','Bavaria','DE');



INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Public Accountant',4200.00,9000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Accounting Manager',8200.00,16000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Administration Assistant',3000.00,6000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('President',20000.00,40000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Administration Vice President',15000.00,30000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Accountant',4200.00,9000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Finance Manager',8200.00,16000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Human Resources Representative',4000.00,9000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Programmer',4000.00,10000.00);

INSERT INTO jobs(job_title,min_salary,max_salary) VALUES ('Marketing Manager',9000.00,15000.00);


INSERT INTO departments(department_name,location_id) VALUES ('Administration',9);

INSERT INTO departments(department_name,location_id) VALUES ('Marketing',8);

INSERT INTO departments(department_name,location_id) VALUES ('Purchasing',7);

INSERT INTO departments(department_name,location_id) VALUES ('Human Resources',5);

INSERT INTO departments(department_name,location_id) VALUES ('Shipping',1);

INSERT INTO departments(department_name,location_id) VALUES ('IT',2);

INSERT INTO departments(department_name,location_id) VALUES ('Public Relations',4);

INSERT INTO departments(department_name,location_id) VALUES ('Sales',4);

INSERT INTO departments(department_name,location_id) VALUES ('Executive',7);

INSERT INTO departments(department_name,location_id) VALUES ('Finance',9);

INSERT INTO departments(department_name,location_id) VALUES ('Accounting',9);


INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('steven','king','steven.king@sqltutorial.org','515.123.4567','1987-06-17',4,24000.00,NULL,24);

INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('neena','kochhar','neena.kochhar@sqltutorial.org','515.123.4568','1989-09-21',5,17000.00,100,25);

INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('Lex','De Haan','lex.de haan@sqltutorial.org','515.123.4569','1993-01-13',5,17000.00,100,26);

INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('alexander','hunold','alexander.hunold@sqltutorial.org','590.423.4567','1990-01-03',9,9000.00,102,25);

INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('bruce','ernst','bruce.ernst@sqltutorial.org','590.423.4568','1991-05-21',9,6000.00,103,24);

INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('david','austin','david.austin@sqltutorial.org','590.423.4569','1997-06-25',9,4800.00,103,26);


select * from departments


select * from employees


INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('steven','king','steven.king@sqltutorial.org','515.123.4567','1987-06-17',4,24000.00,NULL,8);


INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('neena','kochhar','neena.kochhar@sqltutorial.org','515.123.4568','1989-09-21',5,17000.00,100,9);


INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('Lex','De Haan','lex.de haan@sqltutorial.org','515.123.4569','1993-01-13',5,17000.00,100,8);


INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('alexander','hunold','alexander.hunold@sqltutorial.org','590.423.4567','1990-01-03',9,9000.00,102,9);


INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('bruce','ernst','bruce.ernst@sqltutorial.org','590.423.4568','1991-05-21',9,6000.00,103,10);


INSERT INTO employees(first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES ('david','austin','david.austin@sqltutorial.org','590.423.4569','1997-06-25',9,4800.00,103,11);

 53m 9s

 57m 30s

08:39

SELECT

employee_id,

first_name,

last_name,

hire_date,

salary

FROM

employees

ORDER BY

first_name,

last_name DESC;


--- last_name DESC;

--------------------------------

SELECT 

    DISTINCT salary

FROM

    employees

--------------------------------

SELECT 

    employee_id, 

    first_name, 

    last_name, 

    salary

FROM employees1

ORDER BY 

    salary DESC

OFFSET 2 ROWS

FETCH NEXT 2 ROWS ONLY;


--------------------------------


08:53


SELECT

employee_id,

first_name,

last_name,

salary

FROM

employees

WHERE

salary > 14000

ORDER BY

salary DESC;

salary <> 17000

salary != 17000


--------------------------------


sELECT

*

FROM

employees

WHERE

salary < 17000 and department_id = 4

--------------------------------


SELECT 

    employee_id, 

    first_name, 

    last_name, 

    salary

FROM

    employees

WHERE

    salary BETWEEN 2000 AND 8000

--------------------------------


SELECT

employee_id,

first_name,

last_name,

job_id

FROM

employees

WHERE

department_id IN ( 9, 10,11)

--------------------------------


SELECT

employee_id,

first_name,

last_name

FROM

employees

WHERE

first_name LIKE 'Da%';

--------------------------------


SELECT

*

FROM

employees

WHERE

manager_id IS NULL;

--------------------------------


SELECT

*

FROM

employees

WHERE

manager_id IS NOT NULL;

--------------------------------


SELECT

employee_id,

first_name,

last_name,

department_id

FROM

employees

WHERE

department_id NOT IN (8, 9)

--------------------------------


select YEAR(hire_date) from employees

09:25

SELECT 

    first_name,

    last_name,

    hire_date,

    CASE (2022 - YEAR(hire_date))

      

        WHEN 20 THEN '20 years'

        WHEN 25 THEN '25 years'

WHEN 26 THEN '26 years'

  WHEN 27 THEN '27 years'

   WHEN 28 THEN '28 years'

   WHEN 29 THEN '29 years'

        WHEN 30 THEN '30 years'

WHEN 31 THEN '31 years'

WHEN 32 THEN '32 years'

WHEN 33 THEN '33 years'

WHEN 34 THEN '34 years'

WHEN 35 THEN '35 years'

WHEN 36 THEN '36 years'

    END aniversary

FROM

    employees1

-----------------


SELECT REVERSE('abcd') 
SELECT UPPER('Allah-hus-samad');
select UPPER(first_name) from employees
 SELECT  ROUND(157.96,0) 

--------------------------------



Date Functions




SELECT GETDATE(), DAY(GETDATE()) , DAY('2021-05-30 15:46:19.277');
SELECT GETDATE(), MONTH(GETDATE()) , MONTH('20210101'), MONTH('2021-05-30 15:46:19.277');
select convert(varchar, getdate(), 1)







SELECT FORMAT (getdate(), 'dd-MM-yyyy') as date
Date Parts: can use the name or listed abbreviations:

year, yy, yyyy
quarter, qq, q
month, mm, m
dayofyear, dy, y*
day, dd, d*
weekday, dw, w*
week, wk, ww
hour, hh
minute, mi, n
second, ss, s
millisecond, ms
microsecond, mcs
nanosecond, ns



DATE ONLY FORMATS
Format #QueryFormatSample
1select convert(varchar, getdate(), 1)mm/dd/yy12/30/22
2select convert(varchar, getdate(), 2)yy.mm.dd22.12.30
3select convert(varchar, getdate(), 3)dd/mm/yy30/12/22
4select convert(varchar, getdate(), 4)dd.mm.yy30.12.22
5select convert(varchar, getdate(), 5)dd-mm-yy30-12-22
6select convert(varchar, getdate(), 6)dd-Mon-yy30 Dec 22
7select convert(varchar, getdate(), 7)Mon dd, yyDec 30, 22
10select convert(varchar, getdate(), 10)mm-dd-yy12-30-22
11select convert(varchar, getdate(), 11)yy/mm/dd22/12/30
12select convert(varchar, getdate(), 12)yymmdd221230
23select convert(varchar, getdate(), 23)yyyy-mm-dd2022-12-30
31select convert(varchar, getdate(), 31)yyyy-dd-mm2022-30-12
32select convert(varchar, getdate(), 32)mm-dd-yyyy12-30-2022
33select convert(varchar, getdate(), 33)mm-yyyy-dd12-2022-30
34select convert(varchar, getdate(), 34)dd-mm-yyyy30-12-2022
35select convert(varchar, getdate(), 35)dd-yyyy-mm30-2022-12
101select convert(varchar, getdate(), 101)mm/dd/yyyy12/30/2022
102select convert(varchar, getdate(), 102)yyyy.mm.dd2022.12.30
103select convert(varchar, getdate(), 103)dd/mm/yyyy30/12/2022
104select convert(varchar, getdate(), 104)dd.mm.yyyy30.12.2022
105select convert(varchar, getdate(), 105)dd-mm-yyyy30-12-2022
106select convert(varchar, getdate(), 106)dd Mon yyyy30 Dec 2022
107select convert(varchar, getdate(), 107)Mon dd, yyyyDec 30, 2022
110select convert(varchar, getdate(), 110)mm-dd-yyyy12-30-2022
111select convert(varchar, getdate(), 111)yyyy/mm/dd2022/12/30
112select convert(varchar, getdate(), 112)yyyymmdd20221230


TIME ONLY FORMATS
8select convert(varchar, getdate(), 8)hh:mm:ss00:38:54
14select convert(varchar, getdate(), 14)hh:mm:ss:nnn00:38:54:840
24select convert(varchar, getdate(), 24)hh:mm:ss00:38:54
108select convert(varchar, getdate(), 108)hh:mm:ss00:38:54
114select convert(varchar, getdate(), 114)hh:mm:ss:nnn00:38:54:840
 
DATE & TIME FORMATS
0select convert(varchar, getdate(), 0)Mon dd yyyy hh:mm AM/PMDec 30 2022 12:38AM
9select convert(varchar, getdate(), 9)Mon dd yyyy hh:mm:ss:nnn AM/PMDec 30 2022 12:38:54:840AM
13select convert(varchar, getdate(), 13)dd Mon yyyy hh:mm:ss:nnn AM/PM30 Dec 2022 00:38:54:840AM
20select convert(varchar, getdate(), 20)yyyy-mm-dd hh:mm:ss2022-12-30 00:38:54
21select convert(varchar, getdate(), 21)yyyy-mm-dd hh:mm:ss:nnn2022-12-30 00:38:54.840
22select convert(varchar, getdate(), 22)mm/dd/yy hh:mm:ss AM/PM12/30/22 12:38:54 AM
25select convert(varchar, getdate(), 25)yyyy-mm-dd hh:mm:ss:nnn2022-12-30 00:38:54.840
26select convert(varchar, getdate(), 26)yyyy-dd-mm hh:mm:ss:nnn2022-30-12 00:38:54.840
27select convert(varchar, getdate(), 27)mm-dd-yyyy hh:mm:ss:nnn12-30-2022 00:38:54.840
28select convert(varchar, getdate(), 28)mm-yyyy-dd hh:mm:ss:nnn12-2022-30 00:38:54.840
29select convert(varchar, getdate(), 29)dd-mm-yyyy hh:mm:ss:nnn30-12-2022 00:38:54.840
30select convert(varchar, getdate(), 30)dd-yyyy-mm hh:mm:ss:nnn30-2022-12 00:38:54.840
100select convert(varchar, getdate(), 100)Mon dd yyyy hh:mm AM/PMDec 30 2022 12:38AM
109select convert(varchar, getdate(), 109)Mon dd yyyy hh:mm:ss:nnn AM/PMDec 30 2022 12:38:54:840AM
113select convert(varchar, getdate(), 113)dd Mon yyyy hh:mm:ss:nnn30 Dec 2022 00:38:54:840
120select convert(varchar, getdate(), 120)yyyy-mm-dd hh:mm:ss2022-12-30 00:38:54
121select convert(varchar, getdate(), 121)yyyy-mm-dd hh:mm:ss:nnn2022-12-30 00:38:54.840
126select convert(varchar, getdate(), 126)yyyy-mm-dd T hh:mm:ss:nnn2022-12-30T00:38:54.840
127select convert(varchar, getdate(), 127)yyyy-mm-dd T hh:mm:ss:nnn2022-12-30T00:38:54.840

SELECT DATEADD(DAY,1,'2021-01-01') as 'Add 1 Day',
SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff;

--------------------------------


IF (1=1)
PRINT 'IF STATEMENT: CONDITION IS TRUE'
ELSE
PRINT 'ELSE STATEMENT: CONDITION IS FALSE'
ELSE IF (2=3)
PRINT 'ELSE STATEMENT: CONDITION IS FALSE'


IF (1>1)
PRINT 'IF STATEMENT: CONDITION IS TRUE'
ELSE IF (2=3)
PRINT 'ELSE STATEMENT: CONDITION IS FALSE'
ELSE
PRINT 'ELSE STATEMENT'


SELECT
department_id,
COUNT(employee_id) headcount
FROM
employees1
GROUP BY
department_id;


SELECT
department_id,
MAX(salary) salary
FROM
employees1
GROUP BY
department_id;


SELECT
department_id,
MIN(salary) salary
FROM
employees1
GROUP BY
department_id;


16:14

SELECT 
    department_id, SUM(salary)
FROM
    employees1
WHERE SUM(salary) BETWEEN 20000 AND 30000
GROUP BY department_id

SELECT 
    department_id, SUM(salary)
FROM
    employees
GROUP BY department_id
HAVING SUM(salary) BETWEEN 20000 AND 30000

AVG ,MIn, MAX, Count , Sum


SELECT 
    department_id, SUM(salary)
FROM
    employees
GROUP BY department_id
HAVING SUM(salary) BETWEEN 20000 AND 30000
ORDER BY SUM(salary);


ORDER BY SUM(salary) desc ;



SELECT 
    e.first_name,
    e.last_name,
    e.department_id,
    d.department_id,
    d.department_name
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
WHERE
    e.department_id IN (24,25);

16:29

select * from countries

select * from locations


SELECT
c.country_name,
c.country_id,
l.country_id,
l.street_address,
l.city
FROM
countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
c.country_id IN ('US', 'UK', 'CN')



SELECT
c.country_name,
c.country_id,
l.country_id,
l.street_address,
l.city
FROM
countries c
RIGHT JOIN locations l ON l.country_id = c.country_id
WHERE
c.country_id IN ('US', 'UK', 'CN')

--------------




CREATE TABLE sales_organization (
sales_org_id INT PRIMARY KEY,
sales_org VARCHAR (255)
);
CREATE TABLE sales_channel (
channel_id INT PRIMARY KEY,
channel VARCHAR (255)
);
INSERT INTO sales_organization (sales_org_id, sales_org)
VALUES
(1, 'Domestic'),
(2, 'Export'); 
                                         
INSERT INTO sales_channel (channel_id, channel)
VALUES
(1, 'Wholesale'),
(2, 'Retail'),
(3, 'eCommerce'),
(4, 'TV Shopping');

SELECT
sales_org,
channel
FROM
sales_organization
CROSS JOIN sales_channel; 


SELECT 
    e.first_name +' ' + e.last_name AS employee,
    m.first_name +' ' + m.last_name AS manager
FROM
    employees e
        INNER JOIN
    employees m ON m.employee_id = e.manager_id
ORDER BY manager;


15:50

CREATE VIEW employee_contacts AS

SELECT 
    e.first_name +' ' + e.last_name AS employee,
    m.first_name +' ' + m.last_name AS manager
FROM
    employees e
        INNER JOIN
    employees m ON m.employee_id = e.manager_id


CREATE TABLE parts(
    part_id   INT NOT NULL, 
    part_name VARCHAR(100)
);

INSERT INTO 
    parts(part_id, part_name)
VALUES
    (1,'Frame'),
    (2,'Head Tube'),
    (3,'Handlebar Grip'),
    (4,'Shock Absorber'),
    (5,'Fork');


CREATE CLUSTERED INDEX ix_parts_id
ON parts (part_id);  
SELECT 
    part_id, 
    part_name
FROM 
    parts
WHERE 
    part_id = 5;



Declare @TblVariable  TABLE(      
id int,    
Name varchar(20)      
)      
  
insert into @TblVariable values(1,'Sourabh Somani')  
insert into @TblVariable values(2,'Shaili Dashora')  
insert into @TblVariable values(3,'Divya Sharma')  
insert into @TblVariable values(4,'Swati Soni')  
  
Select * from @TblVariable;



CREATE FUNCTION udfNetSale (
    @quantity INT,
    @list_price DEC(10,2),
    @discount DEC(4,2)
)
RETURNS DEC(10,2)
AS 
BEGIN
    RETURN @quantity  @list_price  (1 - @discount);
END;


SELECT 
    dbo.udfNetSale(10,100,1.5) net_sale;
----------------------------
create function FuncTableValue
(@id int)
Returns Table
as
return select * from Table1 where Id = @id


select * from FuncTableValue(3)

------
create function FuncTest
(@qty int , @price int, @discount int)
returns int
as
begin
return @qty * (@price - @discount)
end
select dbo.FuncTest(5,100,5) TotalPriceAlise 
-------------------------

CREATE FUNCTION fnEmpSalary (
    @sal INT
)
RETURNS TABLE
AS
RETURN

SELECT 
       *
    FROM
        employees1
Where
salary = @sal

SELECT 
    dbo.fnEmpSalary(5000);






SELECT * FROM  dbo.fnEmpSalary(4800);



---------------------------------------------------------------------------




CREATE PROCEDURE uspEmpList(@depid int)
AS
BEGIN
SELECT 
        * 
    FROM 
        employees1
where 
department_id = @depid
End;

uspEmpList 8


CREATE TABLE Person (
    PersonID int PRIMARY KEY IDENTITY(1,1),
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
Age INT
)

INSERT INTO Person VALUES('Hayes', 'Corey','123  Wern Ddu Lane','LUSTLEIGH',23)
INSERT INTO Person VALUES('Macdonald','Charlie','23  Peachfield Road','CEFN EINION',45)
INSERT INTO Person VALUES('Frost','Emma','85  Kingsway North','HOLTON',26)
INSERT INTO Person VALUES('Thomas', 'Tom','59  Dover Road', 'WESTER GRUINARDS',51)
INSERT INTO Person VALUES('Baxter','Cameron','106  Newmarket Road','HAWTHORPE',46)
INSERT INTO Person VALUES('Townsend','Imogen ','100  Shannon Way','CHIPPENHAM',20)
INSERT INTO Person VALUES('Preston','Taylor','14  Pendwyallt Road','BURTON',19)
INSERT INTO Person VALUES('Townsend','Imogen ','100  Shannon Way','CHIPPENHAM',18)
INSERT INTO Person VALUES('Khan','Jacob','72  Ballifeary Road','BANCFFOSFELEN',11)

---------------

BEGIN TRAN

UPDATE Person 

SET    Lastname = 'Lucky', 
        Firstname = 'Luke' 
WHERE  PersonID = 1

SELECT @@TRANCOUNT AS OpenTransactions 

COMMIT TRAN 

SELECT @@TRANCOUNT AS OpenTransactions

---------------


BEGIN TRAN

UPDATE Person 
SET    Lastname = 'Donald', 
        Firstname = 'Duck'  WHERE PersonID=2
 
 
SELECT * FROM Person WHERE PersonID=2
 
ROLLBACK TRAN 
 
SELECT * FROM Person WHERE PersonID=2

---------------


BEGIN TRANSACTION 
INSERT INTO Person 
VALUES('Mouse', 'Micky','500 South Buena Vista Street, Burbank','California',43)

SAVE TRANSACTION InsertStatement
DELETE Person WHERE PersonID=3
SELECT * FROM Person   WHERE PersonID=3
ROLLBACK TRANSACTION InsertStatement 
COMMIT
SELECT * FROM Person  WHERE PersonID=3


---------------


CREATE TABLE emergency_contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    relationship VARCHAR(50) NOT NULL,
    employee_id INT NOT NULL
);
CREATE TABLE emergency_contacts (
    id INT  PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    relationship VARCHAR(50) NOT NULL,
    employee_id INT NOT NULL
);

DROP TABLE emergency_contacts;

---------------
CREATE TABLE big_table (
id INT  PRIMARY KEY,
val INT
);


INSERT INTO big_table (id,val)
VALUES
(1,RAND(100000));

TRUNCATE TABLE big_table;

---------------


CREATE SCHEMA customer_services;

SELECT 
    s.name AS schema_name, 
    u.name AS schema_owner
FROM 
    sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY 
    s.name;

CREATE TABLE customer_services.jobs(
    job_id INT PRIMARY KEY IDENTITY,
    customer_id INT NOT NULL,
    description VARCHAR(200),
    created_at DATETIME2 NOT NULL
);

---------------


DECLARE @sql nvarchar(max) --declare variable
DECLARE @empId nvarchar(max) --declare variable for parameter

set @empId = '4' --assign value to parameter variable
set @sql = 'SELECT * FROM employees WHERE employee_iD =' + @empId --build query string with parameter

exec(@sql) --execute sql query


---------------


DECLARE @sql nvarchar(max) --declare variable
DECLARE @dpId nvarchar(max) --declare variable for parameter

set @dpId = '24' --assign value to parameter variable
set @sql = 'SELECT * FROM employees1 WHERE department_id =' + @dpId --build query string with parameter
set @sql = @sql + ' or job_id = ' + '4'
exec(@sql) --execute sql query



---------------



CREATE SEQUENCE SequenceCounter
    AS INT
    START WITH 5
    INCREMENT BY 2;

SELECT NEXT VALUE FOR SequenceCounter

CREATE TABLE Training(
    TrainingId int PRIMARY KEY,
    TrainingName nvarchar(50) NOT NULL,
    TrainingDate date NOT NULL)
);


INSERT INTO Training(TrainingId, TrainingName, TrainingDate)
VALUES(NEXT VALUE FOR SequenceCounter, 'SEO' , '11/23/2022');

INSERT INTO Training(TrainingId, TrainingName, TrainingDate)
VALUES(NEXT VALUE FOR SequenceCounter, 'SQL Server', '11/24/2022');

CREATE SYNONYM Emp FOR Employee;  


INSERT INTO Emp
values('Amit', 'Saha', 'ah23@abc.com', '22545678',  '10/03/2017',  2, 67000, 20);

select * from emp

15:46
-----------------------

Trigger


CREATE TABLE Employee_Audit_Test  
(    
Id int IDENTITY,   
Audit_Action text   
)  
CREATE TABLE Employee  
(  
  Id INT PRIMARY KEY,  
  Name VARCHAR(45),  
  Salary INT,  
  Gender VARCHAR(12),  
  DepartmentId INT  
)  

INSERT INTO Employee VALUES (1,'Steffan', 82000, 'Male', 3),  
(2,'Amelie', 52000, 'Female', 2),  
(3,'Antonio', 25000, 'male', 1),  
(4,'Marco', 47000, 'Male', 2),  
(5,'Eliana', 46000, 'Female', 3)  

CREATE TRIGGER trInsertEmployee   
ON Employee  
FOR INSERT  
AS  
BEGIN  
  Declare @Id int  
  SELECT @Id = Id from inserted  
  INSERT INTO Employee_Audit_Test  
  VALUES ('New employee with Id = ' + CAST(@Id AS VARCHAR(10)) + ' is added at ')  
END  


INSERT INTO Employee VALUES (6,'Peter', 62000, 'Male', 3)  

select * from Employee_Audit_Test

CREATE TRIGGER trDeleteEmployee   
ON Employee  
FOR DELETE  
AS  
BEGIN  
  Declare @Id int  
  SELECT @Id = Id from deleted  
  INSERT INTO Employee_Audit_Test  
  VALUES ('An existing employee with Id = ' + CAST(@Id AS VARCHAR(10)) + ' is deleted')  
END  

DELETE FROM Employee WHERE Id = 2;  



WITH CTE(Name, 
    Salary,
    Gender,
    duplicatecount)
AS (SELECT Name, 
           Salary, 
           Gender, 
           ROW_NUMBER() OVER(PARTITION BY Name, 
                                          Salary, 
                                          Gender
           ORDER BY Id) AS DuplicateCount
    FROM [employee])
SELECT *
FROM CTE;

---------------------



Union

- it will avoid duplicate 
- join two table with same cols
- will return all rows

SELECT
first_name,
last_name
FROM
employees1
UNION
SELECT
first_name,
last_name
FROM
dependents

Union  all

- will return all rows with duplicates

SELECT 
    first_name
FROM
    employees1 
Union  all
SELECT 
    first_name
FROM
    dependents;

INTERSECT

- will return common rows

SELECT
id
FROM
a 
INTERSECT
SELECT
id
FROM
b;
--------------
Unique key

CREATE TABLE users (
    user_id INT Identity(1,1) PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    CONSTRAINT uc_username UNIQUE (username)
);

insert into users values('test1','ttt')

insert into users values('test1','ttt')
insert into users values('test','ttt')


--------------
NOT NULL


CREATE TABLE trainings (
    employee_id INT,
    course_id INT,
    taken_date DATE NOT NULL,
    PRIMARY KEY (employee_id , course_id)
);

INSERT INTO trainings(employee_id,course_id)
VALUES(1,1); ---error

INSERT INTO trainings(employee_id,course_id,taken_date)
VALUES(1,1,GETDATE());

------------------

SELECT 
    first_name
FROM
    employees1
intersect
SELECT 
    first_name
FROM
    dependents;

CREATE TABLE users (
    user_id INT Identity(1,1) PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    CONSTRAINT uc_username UNIQUE (username)
);
insert into users values('test1','ttt')

insert into users values('test1','ttt')

insert into users values('test','ttt')

CREATE TABLE trainings (
    employee_id INT,
    course_id INT,
    taken_date DATE NOT NULL,
    PRIMARY KEY (employee_id , course_id)
);

INSERT INTO trainings(employee_id,course_id)
VALUES(1,1); ---error


INSERT INTO trainings(employee_id,course_id,taken_date)
VALUES(1,1,GETDATE());

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    selling_price int CHECK (selling_price > 0)
);

insert into products values(1,'test',0)

insert into products values(1,'test',50)

-----------------------------

CREATE TABLE ExamResult
(StudentName VARCHAR(70), 
 Subject     VARCHAR(20), 
 Marks       INT
);



INSERT INTO ExamResult
VALUES
('Lily', 
 'Maths', 
 65
);
INSERT INTO ExamResult
VALUES
('Lily', 
 'Science', 
 80
);
INSERT INTO ExamResult
VALUES
('Lily', 
 'english', 
 70
);
INSERT INTO ExamResult
VALUES
('Isabella', 
 'Maths', 
 50
);
INSERT INTO ExamResult
VALUES
('Isabella', 
 'Science', 
 70
);
INSERT INTO ExamResult
VALUES
('Isabella', 
 'english', 
 90
);
INSERT INTO ExamResult
VALUES
('Olivia', 
 'Maths', 
 55
);
INSERT INTO ExamResult
VALUES
('Olivia', 
 'Science', 
 60
);
INSERT INTO ExamResult
VALUES
('Olivia', 
 'english', 
 89
);

 ---ROW_NUMBER will return different rank for same marks
SELECT Studentname, 
       Subject, 
       Marks, 
       ROW_NUMBER() OVER(ORDER BY Marks) RowNumber
FROM ExamResult;

SELECT Studentname, 
       Subject, 
       Marks, 
       DENSE_RANK() OVER( ORDER BY Marks ) Rank
FROM ExamResult


SELECT Studentname, 
       Subject, 
       Marks, 
       RANK() OVER(PARTITION BY Studentname ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Studentname, 
         Rank;

WITH StudentRanks AS
(
  SELECT *, Rank() OVER( ORDER BY Marks desc) AS Ranks
  FROM ExamResult
)
 select * from StudentRanks


 WITH StudentRanks AS
(
  SELECT *, ROW_NUMBER() OVER( ORDER BY Marks desc) AS Ranks
  FROM ExamResult
)
 select * from StudentRanks


SELECT StudentName , Marks 
FROM StudentRanks
WHERE Ranks >= 1 and Ranks <=3
ORDER BY Ranks


----------------------


CREATE TABLE #haro_products (
    product_name VARCHAR(MAX),
    list_price DEC(10,2)
);

INSERT INTO #haro_products values('test',34.43)


SELECT
    *
FROM
    #haro_products;

----------------------------

System stored procedure


exec sp_help  'employee'


-------------------------


SELECT 
    NEWID() AS GUID;


CREATE TABLE customers2(
    customer_id UNIQUEIDENTIFIER DEFAULT NEWID(),
    first_name NVARCHAR(100) NOT NULL,
    last_name NVARCHAR(100) NOT NULL,
    email VARCHAR(200) NOT NULL
);

INSERT INTO 
    customers2(first_name, last_name, email)
VALUES
    ('John','Doe','john.doe@example.com'),
    ('Jane','Doe','jane.doe@example.com');


SELECT 
    customer_id, 
    first_name, 
    last_name, 
    email
FROM 
    customers2;    



Execution plan and performance tuning concepts
back up & restore 
index






Comments

Post a Comment

Popular Posts