SQL SERVER

  • INSERT INTO QueryPanel_employee (Emp_id,Name,Date,SalesInventry,Quantity)VALUES(’09’,0.15,’20180601′,’20180901′,’90’);
  • UPDATE QueryPanel_employee SET Name=’asdf’ WHERE Quantity=’90’;
  • SELECT * FROM QueryPanel_employee;
  • ALTER TABLE QueryPanel_employee
    DROP COLUMN Date;
  • SELECT Emp_id,Name,salesInventry,Quantity FROM QueryPanel_employee;
  • SELECT Name FROM QueryPanel_employee;
    SELECT Name FROM QueryPanel_employee WHERE SalesInventry=’22’;
    ALTER TABLE QueryPanel_employee ADD F_Name varchar(225);
  • CREATE TABLE products (
    productID INT NOT NULL IDENTITY,
    productCode CHAR(3) NOT NULL DEFAULT ”,
    name VARCHAR(30) NOT NULL DEFAULT ”,
    quantity INT NOT NULL DEFAULT 0,
    price DECIMAL(7,2) NOT NULL DEFAULT 99999.99
    );
  • USE lifecycle
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = ‘BASE TABLE’
  • Alter database lifecycle modify Name = qpanel
    ALTER DATABASE lifescycletable MODIFY NAME=sgt;
  • EXEC sp_rename ‘lifescycletable’, ‘lfc’;
  • DROP DATABASE databasename;
    DROP TABLE tablename;
    DROP DATABASE TESTING;
    (MAKING SINGLE USER)
    ALTER DATABASE QueryPanel Set SINGLE_USER WITH Rollback immediate ;
    (MULTI USER)
    ALTER DATABASE QueryPanel SET MULTI_USER
  • CREATE DATABASE Employees_details;
  • CREATE TABLE Employees_all (e_id INT NOT NULL PRIMARY KEY IDENTITY ,
    E_Name CHAR(255), F_name CHAR(255), Joining_date VARCHAR(255),
    salary VARCHAR(255), E_Location VARCHAR(255), Employee_Profile VARCHAR(255),
    vication_date VARCHAR(255));
  • CREATE DATABASE ORG;
  • CREATE TABLE Worker (
    WORKER_ID INT NOT NULL PRIMARY KEY IDENTITY,
    FIRST_NAME CHAR(25),
    LAST_NAME CHAR(25),
    SALARY VARCHAR(15),
    JOINING_DATE DATETIME,
    DEPARTMENT CHAR(25)
    );
  • ALTER TABLE “Worker” ALTER COLUMN “WORKER_ID” “INT”;
  • INSERT INTO Worker
    (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
    (001, ‘Monika’, ‘Arora’, 100000, ’14-02-20 09.00.00′, ‘HR’),
    (002, ‘Niharika’, ‘Verma’, 80000, ’14-06-11 09.00.00′, ‘Admin’),
    (003, ‘Vishal’, ‘Singhal’, 300000, ’14-02-20 09.00.00′, ‘HR’),
    (004, ‘Amitabh’, ‘Singh’, 500000, ’14-02-20 09.00.00′, ‘Admin’),
    (005, ‘Vivek’, ‘Bhati’, 500000, ’14-06-11 09.00.00′, ‘Admin’),
    (006, ‘Vipul’, ‘Diwan’, 200000, ’14-06-11 09.00.00′, ‘Account’),
    (007, ‘Satish’, ‘Kumar’, 75000, ’14-01-20 09.00.00′, ‘Account’),
    (008, ‘Geetika’, ‘Chauhan’, 90000, ’14-04-11 09.00.00′, ‘Admin’);
  • (ALTER MODIFY)
  • SP_RENAME ‘Worker.WORKER_ID’, ‘WID ‘, ‘COLUMN’;
  • SP_RENAME ‘Worker.WORKER_ID’, ‘WID ‘, ‘COLUMN’;
  • (Modify Datatype)
    ALTER TABLE Worker ALTER COLUMN JOINING_DATE DATE;
  • CREATE TABLE emp ( empno INT PRIMARY KEY, ename VARCHAR(20),
    job VARCHAR(30),mgr INT NULL, hiredate DATETIME, sal NUMERIC(8,2) NULL,
    dept VARCHAR(30));
  • CREATE DATABASE QueryPanel;
  • CREATE TABLE emp (
    empno INT PRIMARY KEY,
    ename VARCHAR(10),
    job VARCHAR(9),
    mgr INT NULL,
    hiredate DATETIME,
    sal NUMERIC(7,2),
    comm NUMERIC(7,2) NULL,
    dept INT)
  • insert into emp values
    (1,’JOHNSON’,’ADMIN’,6,’12-17-1990′,18000,NULL,4),
    (2,’HARDING’,’MANAGER’,9,’02-02-1998′,52000,300,3),
    (3,’TAFT’,’SALES I’,2,’01-02-1996′,25000,500,3),
    (4,’HOOVER’,’SALES I’,2,’04-02-1990′,27000,NULL,3),
    (5,’LINCOLN’,’TECH’,6,’06-23-1994′,22500,1400,4),
    (6,’GARFIELD’,’MANAGER’,9,’05-01-1993′,54000,NULL,4);
  • CREATE TABLE dept (
    deptno INT NOT NULL,
    dname VARCHAR(14),
    loc VARCHAR(13))
  • insert into dept
    values (1,’ACCOUNTING’,’ST LOUIS’),
    (2,’RESEARCH’,’NEW YORK’),
    (3,’SALES’,’ATLANTA’),
    (4, ‘OPERATIONS’,’SEATTLE’);
  • SELECT * FROM dept;
  • SELECT * FROM emp;
  • SELECT ename,job FROM emp WHERE dept=4;
  • SELECT ename=’HOOVER’,job=’SALES l’ FROM emp WHERE sal=27000;
  • SELECT * FROM emp WHERE empno=1;
  • (Greater than 22000)
    SELECT ename FROM emp WHERE sal>=22000;
    (Greater than 22000)
    SELECT ename FROM emp WHERE sal>=52000;
  • (Less Than)
  • SELECT ename FROM emp WHERE sal<=2000;
  • SELECT ename FROM emp WHERE sal<=22000;
  • SELECT ename,sal FROM emp WHERE sal<=22000;
  • SELECT ename FROM emp WHERE sal>=52000;
  • CREATE TABLE CUSTOMERS(
    ID INT NOT NULL,
    NAME VARCHAR (20) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR (25) ,
    SALARY DECIMAL (18, 2),
    PRIMARY KEY (ID));
  • DESC Cmd verify table created seceffully or not;
  • DESC CUSTOMERS;
  • (GREATER THAN AND LESS THAN)
    SELECT ID, NAME, SALARY
    FROM CUSTOMERS
    WHERE SALARY > 2000 AND age < 25;
  • SELECT NAME,SALARY FROM CUSTOMERS WHERE SALARY=2000 AND NAME=’Ramesh’;
  • SELECT ADDRESS,SALARY FROM CUSTOMERS WHERE SALARY=2000 OR ADDRESS=’Delhi’;
  • SELECT ADDRESS,SALARY FROM CUSTOMERS WHERE ADDRESS=’Kota’ OR ADDRESS=’Delhi’;
  • (NOT IN)
  • SELECT NAME,SALARY,ADDRESS FROM CUSTOMERS WHERE NOT ADDRESS=’DELHI’;
  • (Combining AND OR)
  • SELECT AGE,SALARY FROM CUSTOMERS WHERE SALARY=2000 AND(ADDRESS=’KOTA’ OR ADDRESS=’MOMBAI’);
  • SELECT NAME,AGE FROM CUSTOMERS WHERE AGE=’20’ AND(ADDRESS=’DELHI’ OR ADDRESS=’MOMBAI’);
  • (Modify Add Multiple Colums)
  • ALTER TABLE CUSTOMERS ADD CurrentAdress varchar(50),Gender varchar(20);
  • SELECT NAME,ADDRESS FROM CUSTOMERS WHERE ADDRESS=’DELHI’ AND NOT ADDRESS=’KOTA’;
  • (UPDATE)
  • UPDATE CUSTOMERS SET ADDRESS=’Ahme-bad’ WHERE ID=1;
  • UPDATE CUSTOMERS SET ADDRESS=’Ahmebad’ ,SALARY=’24000′ WHERE ID=’1′;
  • UPDATE CUSTOMERS SET DEPARTMENT=’ORACLE’,CurrentAdress=’Delhi’,Gender=’Male’ WHERE ID=’3′;
  • (UPDATE USING AND OPERATOR)
  • UPDATE CUSTOMERS SET DEPARTMENT=’F.A’ WHERE SALARY IN(24000,1500) AND ID IN (1,2);
  • (UPDATE USING OR OPERATOR)
  • UPDATE CUSTOMERS SET DEPARTMENT=’TRAINEE’ WHERE SALARY IN(4500,8500) OR ID IN (5,6);