POSTGRE SQL Day-2

How do you insert data into a table?

Table:

EMPLOYEES(NAME, DEPARTMENT, SALARY)

Values:

("JOHN DOE", "IT", 60000)

(a)  

Write a SQL query to display the NAMES of EMPLOYEES who are assigned to more than one PROJECT.

INNER JOIN

Tables:

EMPLOYEES E(EMPLOYEEID, NAME)
PROJECTS P(PROJECTID, EMPLOYEEID)

The output should include the employee's NAME and the number of projects assigned as PROJECTCOUNT.

(a)  

Write a PostgreSQL query to find the names of students who have scored more than 90 in any subject, using a subquery(NO JOIN)

Tables:

STUDENTS(STUDENTID, NAME, AGE)
MARKS(MARKID, STUDENTID, SUBJECTS, SCORE)

(a)  

Write a PostgreSQL query to display the names of customers who have placed at least one order. Use a subquery, not a join.

Tables:

• CUSTOMERS(CUSTOMERID, NAME, CITY)

• ORDERS(ORDERID, CUSTOMERID, ORDERDATE, TOTALAMOUNT)

(a)  

Query to Find 2nd Highest Salary Using Subquery:

Table:

EMPLOYEES(EMPID, NAME, SALARY)

THE COLUMN NAME AS SECONDHIGHESTSALARY

(a)  

Query to Find 3rd Minimum Salary Using Subqueries

Table:

EMPLOYEES(EMPID, NAME, SALARY) COLUMN NAME AS THIRD_MIN_SALARY

(a)  

List all students who have never taken any exam. Use a subquery with NOT IN.

Tables:

• STUDENTS(STUDENTID, NAME)

• EXAMS(EXAMID, STUDENTID, SCORE)

(a)  

Write a SQL query to display each student's NAME along with their total score from all exams.
The output should include columns:
INNER JOIN

• NAME — the student's name

• TOTAL_SCORE — the sum of all their exam scores

Use GROUP BY and the aggregate function SUM().

Tables:

• STUDENTS S(STUDENTID, NAME)

• EXAMS E(EXAMID, STUDENTID, SCORE)

(a)  

Write a SQL query to display each STUDENTID along with their total score in all exams.
The output should include the columns:

• STUDENTID

• TOTAL_SCORE — the sum of all their exam scores

Use only the EXAMS table, with GROUP BY and the SUM() function.

Table:

EXAMS(EXAMID, STUDENTID, SCORE)

(a)  

QUESTION:

Write a SQL query to display each CATEGORY along with the number of products in that category.
The output should include:

• CATEGORY

• PRODUCT_COUNT — count of products

TABLE:

PRODUCTS(PRODUCTID, CATEGORY, PRICE)

(a)