create table EMPDETAILS ( EMPID INT, EMPNAME VARCHAR(500), SALARY FLOAT, DESIGNATION VARCHAR(100) ) ----- INSERT INTO EMPDETAILS VALUES(101,'AMIT',30000,'MANAGER') INSERT INTO EMPDETAILS VALUES(301,'AMIT1',30000,'MANAGER'), (302,'AMIT2',30000,'MANAGER'),(303,'AMIT3',30000,'MANAGER') INSERT INTO EMPDETAILS(EMPID,EMPNAME) VALUES(501,'SAMBIT') ----- SELECT * INTO EMP FROM EMPDETAILS SELECT * FROM EMP SELECT * FROM DATABASENAME.DBO.TABLENAME SELECT * INTO PRODUCTS FROM AdvDW.DBO.DimProduct SELECT * INTO BNYPRODUCTS FROM AdvDW.DBO.DimProduct --------- CREATE TABLE ORDERDETAILS ( ORDERID INT IDENTITY(1,1), AMOUNT FLOAT, ORDERDATETIME DATETIME ) INSERT INTO ORDERDETAILS VALUES(107.86,'10/10/2023') INSERT INTO ORDERDETAILS VALUES(89.86,'01/01/2022') SELECT * FROM ORDERDETAILS INSERT INTO ORDERDETAILS VALUES(55.86,'01/01/2022') ---------- CREATE TABLE EMPLOYEE ( EMPID INT CONSTRAINT PK_EMPID PRIMARY KEY, EMPNAME VARCHAR(100) NOT NULL, EMAILID VARCHAR(100) UNIQUE, MOBILE VARCHAR(10) CONSTRAINT U_MOB UNIQUE NOT NULL, SALARY FLOAT NOT NULL, ) INSERT INTO EMPLOYEE VALUES(1,'SAMIR','sam@gmail.com', '8877665544',20000) ---------- create table EMPS ( EMPID INT IDENTITY(1,1) PRIMARY KEY, EMPNAME VARCHAR(100) UNIQUE, GENDER VARCHAR(10) CHECK(GENDER IN('M','F')), SALARY FLOAT CONSTRAINT C_SAL CHECK(SALARY > 50000), DATEOFJOINING DATETIME DEFAULT('01/01/2024'), STATUS VARCHAR(100) CHECK(STATUS IN('ACTIVE','INACTIVE')) DEFAULT 'ACTIVE' ) SELECT * FROM EMPS INSERT INTO EMPS(EMPNAME,GENDER,SALARY) VALUES('KULDEEP','MMM',60000) ---------- SELECT * FROM EMPDETAILS update EMPDETAILS set SALARY = 25000 WHERE EMPID IN(501,107) ALTER TABLE EMPDETAILS add constraint e_sc check(salary > 20000) ALTER TABLE EMPDETAILS ALTER COLUMN EMPID INT NOT NULL ALTER TABLE EMPDETAILS ADD CONSTRAINT PK_EMPID1 PRIMARY KEY(EMPID) ALTER TABLE table_name DROP CONSTRAINT CONSTRAINT_NAME ALTER TABLE EMPDETAILS DROP CONSTRAINT PK_EMPID1 -------------------- CREATE TABLE EMPLOYEE ( EMPID INT CONSTRAINT PK_EMP PRIMARY KEY, EMPNAME VARCHAR(100) CONSTRAINT U_NAM UNIQUE, GENDER VARCHAR(10) constraint C_GENDER CHECK (GENDER IN ('MALE','FEMALE')), DATEOFBIRTH DATETIME, DATEOFJOINING DATETIME CONSTRAINT D_DJ DEFAULT GETDATE(), SALARY FLOAT constraint C_SAL1 CHECK (SALARY > 10000), TAX FLOAT CONSTRAINT D_DT DEFAULT 100, DEPARTMENT VARCHAR(100), DESIGNATION VARCHAR(100), CITY VARCHAR(100) ) INSERT INTO EMPLOYEE VALUES(1,'SANDEEP','MALE','01/15/1999','01/18/2021',20000,1000, 'IT','DEVELOPER','MUMBAI') INSERT INTO EMPLOYEE VALUES(2,'KAILASH','MALE','05/20/1998','01/18/1999',30000,1000, 'IT','DEVELOPER','PUNE') INSERT INTO EMPLOYEE VALUES(3,'SONALI','FEMALE','08/21/1999','01/20/2021',15000,1000, 'ADMIN','OFFICER','MUMBAI') INSERT INTO EMPLOYEE VALUES(4,'ROSHINI','FEMALE','05/20/1997','01/21/2000',16000,1000, 'ADMIN','OFFICER','NASHIK') INSERT INTO EMPLOYEE VALUES(11,'UMESH','MALE','02/25/1996','04/11/2021',25000,1000, 'HR','EXECUTIVE','MUMBAI') INSERT INTO EMPLOYEE VALUES(12,'JAYESH','MALE','05/11/1995','01/18/2018',35000,1000, 'HR','MANAGER','PUNE') INSERT INTO EMPLOYEE VALUES(13,'ISHITA','FEMALE','07/07/1999','01/20/2011',18000,1000, 'FINANCE','ACCOUNTANT','MUMBAI') INSERT INTO EMPLOYEE VALUES(14,'KAVITA','FEMALE','09/08/1997','01/21/2012',19000,1000, 'FINANCE','MANAGER','PUNE') INSERT INTO EMPLOYEE VALUES(15,'RADHA','FEMALE','05/25/1998','01/19/1999',16500,1000, 'ADMIN','OFFICER','NASHIK') ------------ Select * from EMPLOYEE Select empid,empname from EMPLOYEE Select * from EMPLOYEE where GENDER = 'MALE' AND DEPARTMENT='IT' --SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME (ASC/DESC) (DEFAULT ASC) Select * from EMPLOYEE ORDER BY SALARY DESC Select * from EMPLOYEE ORDER BY GENDER ASC,DEPARTMENT ASC,SALARY DESC select SUM(SALARY) AS TOTAL from EMPLOYEE Select * from EMPLOYEE WHERE DEPARTMENT='IT' Select * from EMPLOYEE WHERE DEPARTMENT IN('IT','HR') ----------- --SELECT DISTINCT * from table_name Select DEPARTMENT from EMPLOYEE Select distinct DEPARTMENT from EMPLOYEE --SELECT TOP [N] * FROM TABLENAME select * from EMPLOYEE order by SALARY desc select Top 5 * from EMPLOYEE order by SALARY desc select Top 1 * from EMPLOYEE order by SALARY desc SELECT * FROM TABLENAME ORDER BY COLNAME OFFSET N ROWS FETCH NEXT N ROWS ONLY; SELECT * FROM EMPLOYEE ORDER BY SALARY desc OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY; ------------- SELECT * FROM EMPLOYEE order by SALARY asc offset 4 rows fetch next 2 rows only SELECT * FROM EMPLOYEE order by SALARY asc offset 4 rows select DEPARTMENT,gender from EMPLOYEE select distinct DEPARTMENT,gender from EMPLOYEE SELECT * FROM EMPLOYEE ------------------- --Is used to search string patterns in the query. SELECT * FROM EMPLOYEE WHERE EMPNAME='SANDEEP' SELECT * FROM EMPLOYEE WHERE EMPNAME LIKE 'S%' SELECT * FROM EMPLOYEE WHERE EMPNAME LIKE 'S%P' SELECT * FROM EMPLOYEE WHERE EMPNAME LIKE '[SK]%' SELECT * FROM EMPLOYEE WHERE EMPNAME LIKE 'S%D%P' SELECT * FROM EMPLOYEE WHERE DEPARTMENT LIKE 'I%' % (percent sign) Any string including an empty one. 'D%': string starting with D _ (underscore) A single character '_D%': string where second character is D [] A single character from a list '[AC]%': string where first character is A or C [] A single character from a range '[0-9]%': string where first character is a digit [^] A single character that is not in the list or range '[^0-9]%': string where first character is not a digit -------------- SELECT GENDER,COUNT (EMPID) AS EMPLOYEECOUNT FROM EMPLOYEE GROUP BY GENDER HAVING COUNT (EMPID) > 4 SELECT DEPARTMENT,SUM(SALARY) AS TOTALSALARY FROM EMPLOYEE --WHERE SUM(SALARY) > 45000 GROUP BY DEPARTMENT HAVING SUM(SALARY) > 45000 SELECT *, SUM(SALARY) OVER(PARTITION BY DEPARTMENT) AS TOTALSALARY FROM EMPLOYEE --------------- SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS SNO, * FROM EMPLOYEE) E WHERE E.SNO=3 SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS SNO, * FROM EMPLOYEE SELECT RANK() OVER(ORDER BY SALARY DESC) AS SNO, * FROM EMPLOYEE SELECT DENSE_RANK() OVER(ORDER BY SALARY DESC) AS SNO, * FROM EMPLOYEE -------------- SELECT RANK() OVER(ORDER BY SALARY DESC) AS SNO, * FROM EMPLOYEE SELECT DENSE_RANK() OVER(ORDER BY SALARY DESC) AS SNO, * FROM EMPLOYEE SELECT RANK() OVER(ORDER BY salary desc) AS SNO, * FROM EMPLOYEE ------------------- CREATE TABLE COUNTRY(COUNTRYID INT PRIMARY KEY, COUNTRYNAME VARCHAR(100), COUNTRYCAPITAL VARCHAR(100)) INSERT INTO COUNTRY VALUES(1,'INDIA','DELHI') CREATE TABLE STATES(STATEID INT PRIMARY KEY, STATENAME VARCHAR(100), STATECAPITAL VARCHAR(100), COUNTRYID INT FOREIGN KEY REFERENCES COUNTRY(COUNTRYID)) INSERT INTO STATES VALUES(1,'MH','MUMBAI',1)