product_sales, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'
CREATE TABLE CATEGORY (
    ID INT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    DESCRIPTION VARCHAR(100) NULL,
    ADDEDON DATETIME DEFAULT GETDATE() NOT NULL,
    MODIFIED DATETIME NULL);
    
       INSERT INTO CATEGORY (ID,NAME)
       VALUES
       (01,'FRUITS'),
       (02,'STATIONARY'),
       (03,'VEGETABLES'),
       (04,'EQUIPMENTS');
       
 SELECT * FROM CATEGORY;     
 
     CREATE TABLE LOCATION (
     ID SMALLINT PRIMARY KEY,
     NAME VARCHAR(50) NOT NULL,
     DISTRICT VARCHAR(100) NULL,
     STATE VARCHAR(100) NULL,
     AREA VARCHAR(100) NULL,
     ADDEDON DATETIME DEFAULT GETDATE() NOT NULL,
     MODIFIED DATETIME NULL);
 
     INSERT INTO LOCATION(ID,NAME,DISTRICT,STATE)
     VALUES
     (1,'MANPADA','THANE','MAHARASHTRA'),
     (2,'LBS MARG','VIKHROLI','MAHARASHTRA'),
     (3,'AIROLI', 'NAVI MUMBAI','MAHARASHTRA');
 
 SELECT * FROM LOCATION;
     
     
     CREATE TABLE SALETYPE (
    ID TINYINT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    DESCRIPTION VARCHAR(100) NULL,
    ADDEDON DATETIME DEFAULT GETDATE() NOT NULL,
    MODIFIED DATETIME NULL);     
    INSERT INTO SALETYPE(ID,NAME)
    VALUES
    (1,'INSIDE SALES'),
    (2,'INSIDE SALES'),
    (3,'OUTSIDE SALES');
    
SELECT * FROM SALETYPE;

CREATE TABLE PRODUCTS (
    ID INT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    CATEGORYID INT FOREIGN KEY REFERENCES CATEGORY(ID),
    DESCRIPTION VARCHAR(100) NULL,
    LOCATION VARCHAR(150) NOT NULL,
    PRICE FLOAT NOT NULL,
    COST FLOAT NOT NULL,
    ADDEDON DATETIME DEFAULT GETDATE() NOT NULL,
    MODIFIEDON DATETIME NULL);
    
    INSERT INTO PRODUCTS(ID,NAME,CATEGORYID,LOCATION,PRICE,COST)
    VALUES
    (01,'PEN',2,'THANE',25,50),
    (02,'APPLE',1,'NAVI MUMBAI',100,130),
    (03,'CARROT',3,'NAVI MUMBAI',50,45),
    (04,'PRINTER',4,'NAVI MUMBAI',6000,8000),
    (05,'TELEPHONE SET',4,'THANE',7500,9000);
    
    SELECT * FROM PRODUCTS;
    
    CREATE TABLE USERS(
       ID INT PRIMARY KEY,
       NAME VARCHAR(50) NOT NULL,
       DOB DATE NOT NULL,
       EMAIL VARCHAR(150) NOT NULL,
       ADDRESS VARCHAR(400) NOT NULL,
       USERTYPE VARCHAR(30) NOT NULL,
       ADDEDON DATETIME DEFAULT GETDATE() NOT NULL,
       MODIFIEDON DATETIME NULL);
    
       ALTER TABLE USERS
       ALTER COLUMN DOB DATETIME;
       
       INSERT INTO USERS(ID,NAME,DOB,EMAIL,ADDRESS,USERTYPE)
       VALUES
       (001,'ROSE',1992-05-07,'rose@gmail.com','#22,MUMBAI,THANE','LEVEL2'),
       (002,'JOHN',1987-04-05,'john@gmail.com','#72,MUMBAI,GHATKOPAR','LEVEL2'),
       (003,'SHON',2000-07-07,'shon@gmail.com','#82,MUMBAI,VASHI','LEVEL1'),
       (004,'JACK',2001-11-09,'jack@gmail.com','#91,MUMBAI,AIROLI','LEVEL1'),
       (005,'RITA',2004-06-21,'rita@gmail.com','#43,MUMBAI,VIKHROLI','LEVEL1');
       
       
 SELECT * FROM USERS;
 
      ALTER TABLE USERS
      ALTER COLUMN DOB DATE;
    
    UPDATE USERS
    SET DOB='1992-05-07'
    WHERE USERS.ID=1;

UPDATE USERS
    SET DOB='1987-04-05'
    WHERE USERS.ID=2;

UPDATE USERS
    SET DOB='2000-07-07'
    WHERE USERS.ID=3;

SELECT* FROM USERS;
    UPDATE USERS
    SET DOB='2001-11-09'
    WHERE USERS.ID=4;

SELECT*FROM USERS;
    UPDATE USERS
    SET DOB='2004-06-21'
    WHERE USERS.ID=5;
    SELECT*FROM USERS;
        
    CREATE TABLE SALE(
    ID BIGINT PRIMARY KEY,
    SALEDATE DATETIME DEFAULT GETDATE() NOT NULL,
    SOLDBY INT FOREIGN KEY REFERENCES USERS(ID),
    AMOUNT DECIMAL(18,2) NOT NULL,
    SALECODE VARCHAR(150) NULL,
    LOCATION_ID SMALLINT FOREIGN KEY REFERENCES LOCATION(ID),
    SALETYPE TINYINT FOREIGN KEY REFERENCES SALETYPE(ID),
    DISCOUNT DECIMAL(18,2) NULL,
    ADDEDON DATETIME DEFAULT GETDATE() NOT NULL,
    ADDEDBY INT FOREIGN KEY REFERENCES USERS(ID),
    MODIFIEDON DATETIME NULL,
    MODIFIEDBY INT FOREIGN KEY REFERENCES USERS(ID));
    
    INSERT INTO SALE(ID,SOLDBY,AMOUNT,LOCATION_ID,SALETYPE,ADDEDBY)
    VALUES
    (1,01,9000,1,3,01),
    (2,02,650,2,2,02),
    (3,01,8000,1,1,01),
    (4,02,650,1,1,02),
    (5,03,9000,2,1,03),
    (6,03,250,2,1,03),
    (7,04,450,1,1,04),
    (8,01,8000,3,1,01),
    (9,04,450,3,1,04),
    (10,01,9000,1,3,01),
    (11,02,650,2,2,02),
    (12,02,650,2,2,02);
    
 SELECT * FROM SALE;   
     
     SELECT *FROM PRODUCTS;
EXEC sp_rename 'PRODUCTS.PRICE' , 'COSTS' , 'COLUMN';
EXEC sp_rename 'PRODUCTS.COST' , 'PRICE' , 'COLUMN';
EXEC sp_rename 'PRODUCTS.COSTS' , 'COST' , 'COLUMN';
SELECT* FROM PRODUCTS;

CREATE TABLE SALEDETAILS(
ID BIGINT PRIMARY KEY,
SALEID BIGINT FOREIGN KEY REFERENCES SALE(ID),
PRODUCTID INT FOREIGN KEY REFERENCES PRODUCTS(ID),
QUANTITY SMALLINT NOT NULL,
SOLDBY INT FOREIGN KEY REFERENCES USERS(ID) NOT NULL,
ADDEDON DATETIME DEFAULT GETDATE() NOT NULL,
ADDEDBY INT FOREIGN KEY REFERENCES USERS(ID),
MODIFIEDON DATETIME NULL,
MODIFIEDBY INT FOREIGN KEY REFERENCES USERS(ID) NULL);
INSERT INTO SALEDETAILS(ID,SALEID,PRODUCTID,QUANTITY,SOLDBY,ADDEDBY)
VALUES
(1,1,5,2,1,1),
(2,2,2,5,2,2),
(3,3,5,1,1,1),
(4,4,2,5,2,2),
(5,5,5,1,3,3),
(6,6,1,10,3,3),
(7,7,3,10,4,4),
(8,8,4,1,1,1),
(9,9,1,10,4,4),
(10,10,5,1,1,1),
(11,11,2,5,2,2),
(12,12,2,5,2,2);

SELECT LOCATION.DISTRICT,SUM(SALE.AMOUNT) AS TOTAL_SALES FROM SALE INNER JOIN LOCATION ON SALE.LOCATION_ID=LOCATION.ID GROUP BY LOCATION.DISTRICT;

SELECT USERS.ID,USERS.NAME,SUM(SALE.AMOUNT) AS TOTAL_SALES,DATEDIFF(YY,USERS.DOB,GETDATE()) AGE FROM USERS LEFT JOIN SALE ON USERS.ID=SALE.SOLDBY WHERE DATEDIFF(YY,USERS.DOB,GETDATE())<21 GROUP BY USERS.ID,USERS.NAME,USERS.DOB;

View schema
Execution time: 0,16 sec, rows selected: 64, rows affected: 49, absolute service time: 0,42 sec, absolute service time: 0,42 sec