Computer Science
Answer
| Primary key | Unique constraints |
|---|---|
| A Primary key is a set of one or more attributes/fields which uniquely identifies a tuple/row in a table. | UNIQUE constraint ensures that no two rows have the same value in the specified column(s). |
| Primary Keys do not allow NULL values. | Unique Constraint allows NULL values. |
| There can be only one Primary Key in a table. | Multiple Unique Constraints can exist in a table. |
Related Questions
Define candidate key.
Define foreign key.
Consider the following EMP and DEPT tables:
Table: EMP
EmpNo EmpName City Designation DOJ Sal Comm DeptID 8369 SMITH Mumbai CLERK 1990-12-18 800.00 NULL 20 8499 ANYA Varanasi SALESMAN 1991-02-20 1600.00 300.00 30 8521 SETH Jaipur SALESMAN 1991-02-22 1250.00 500.00 30 8566 MAHADEVAN Delhi MANAGER 1991-04-02 2985.00 NULL 20 Table: DEPT
DeptID DeptName MgrID Location 10 SALES 8566 Mumbai 20 PERSONNEL 9698 Delhi 30 ACCOUNTS 4578 Delhi 40 RESEARCH 8839 Bengaluru Write the SQL command to get the following:
(a) Show the minimum, maximum and average salary of managers.
(b) Count the number of clerks in the organization.
(c) Display the designation-wise list of employees with name, salary and date of joining.
(d) Count the number of employees who are not getting commission.
(e) Show the average salary for all departments having salary > 2000.
(f) List the count of employees grouped by DeptID.
(g) Display the maximum salary of employees in each department.
(h) Display the name of employees along with their designation and department name.
(i) Count the number of employees working in ACCOUNTS department.
Write SQL commands for (i) to (vi) and write output for (vii) on the basis of PRODUCTS relation given below:
Table: PRODUCTS
PCODE PNAME COMPANY PRICE STOCK MANUFACTURE WARRANTY P001 TV BPL 10000 200 2018-01-12 3 P002 TV SONY 12000 150 2017-03-23 4 P003 PC LENOVO 39000 100 2018-04-09 2 P004 PC COMPAQ 38000 120 2019-06-20 2 P005 HANDYCAM SONY 18000 250 2017-03-23 3 (i) To show details of all PCs with stock more than 110.
(ii) To list the company which gives warranty of more than 2 years.
(iii) To find stock value of the BPL company where stock value is the sum of the products of price and stock.
(iv) To show number of products from each company.
(v) To count the number of PRODUCTS which shall be out of warranty on 20-NOV-2020.
(vi) To show the PRODUCT name of the products which are within warranty as on date.
(vii) Give the output of the following statements:
(a) SELECT COUNT(DISTINCT COMPANY) FROM PRODUCTS;
(b) SELECT MAX(PRICE) FROM PRODUCTS WHERE WARRANTY <= 3;