Postgree SQL Day-1

Which SQL statement is used to select all records from a table named 'CUSTOMERS'?(ANSWER MUST BE WRITTEN IN CAPITAL LETTERS ONLY, NOT IN SMALL LETTERS)

Filter records using WHERE

Retrieves EMPLOYEES who work in the IT DEPARMENT?(ANSWER MUST BE WRITTEN IN CAPITAL LETTERS ONLY, NOT IN SMALL LETTERS)

Use LIKE for pattern matching

Finds CUSTOMERS whose CUSTOMERNAME start with "A".(ANSWER MUST BE WRITTEN IN CAPITAL LETTERS ONLY, NOT IN SMALL LETTERS)

Lists PRODUCTS ordered by PRICE in descending order.(ANSWER MUST BE WRITTEN IN CAPITAL LETTERS ONLY, NOT IN SMALL LETTERS and SPACE)

Aggregate function with GROUP BY

Write a SQL query to COUNT THE NUMBER OF EMPLOYEES IN EACH DEPARTMENT and name the count column as TOTALEMPLOYEES (THE ANSWER MUST BE WRITTEN IN CAPITAL LETTERS ONLY, NO SMALL LETTERS).

Delete a record

Deletes the PRODUCTNAME "PEPSI" from the PRODUCTS table.(ANSWER MUST BE WRITTEN IN CAPITAL LETTERS ONLY, NOT IN SMALL LETTERS)

Write a query to retrieve employee names and their department names.(ANSWER MUST BE WRITTEN IN CAPITAL LETTERS ONLY, NOT IN SMALL LETTERS)

Tables:

• EMPLOYEES E(EMPLOYEEID, NAME, DEPARTMENTID)

  • DEPARTMENTS D(DEPARTMENTID, DEPARTMENTNAME)

Use a Innerjoin to get the total QUANTITY sold per product.(ANSWER MUST BE WRITTEN IN CAPITAL LETTERS ONLY, NOT IN SMALL LETTERS)

• PRODUCTS P(PRODUCTID, PRODUCTNAME)

• SALES S(PRODUCTID, QUANTITY) ALIAS SUM COLUMN Name was TOTALSOLD

Get customers who ordered a specific product (say ‘Milk’). inner join(ANSWER MUST BE WRITTEN IN CAPITAL LETTERS ONLY, NOT IN SMALL LETTERS)

Tables:

• ORDERS O(ORDERID, CUSTOMERID)

• ORDERDETAILS OD(ORDERID, PRODUCTID)

• PRODUCTS P(PRODUCTID, PRODUCTNAME)

• CUSTOMERS C(CUSTOMERID, CUSTOMERNAME)

Stored Procedure for Insert (CRUD)

CREATE PROCEDURE InsertEmployee

@Name NVARCHAR(50),

@Salary DECIMAL(10,2),

@DepartmentID INT

AS

BEGIN

INSERT INTO Employees (Name, Salary, DepartmentID)

VALUES (@Name, @Salary, @DepartmentID);

END;

(VALUES : Name= John Doe, Salary =60000, DepartmentID = 2)