Question

Write and run SQL statements to complete the following tasks

1. Show the details of the vendors who are located in area code 615.
2. Show the details of the products that do not have a value for the attribute v_code.
3. Show the details of the invoices whose subtotal is greater than 25 but less than 75.
4. Show the details of the invoice who has the minimum subtotal.
5. Show the codes and names of the vendors who supplied products.
6. Using EXCEPT show the codes of the vendors who did not supply any products.
7. Using ‘NOT IN’ show the codes and names of the vendors who did not supply any products.
8. List the codes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.
9. List the names and codes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.
10. Add a new attribute (field) status varchar(6) to the EMP table.
11. Update status for employee '100' to 'Temporary'.
12. Using inner join, list the details of the products whose line price is greater than 100.

You are required to answer

1.   The SQL statements for each query, which should be copied and pasted into word.

2. tell all the sql command for each question?

tables: -

VENDOR TABLE

V_CODE	V_NAME	V_CONTACT	V_ARAECODE	V_PHONE	V_STATE	V_ORDER

PRODUCT TABLE

P_CODE	P_DESCRIPT	P_INDATE	P_QOH	P_MIN	P_MIN	P_DISCOUNT	V_CODE

CUSTOMER TABLE:

CUS_CODE	CUS_LNAME	CUS_FNAME	CUS_INITIAL	CUS_AREACODE	CUS_PHONE	CUS_BALANCE

INVOICE TABLE:

INV_NUM	CUS_CODE	INV_DATE	INV_SUBTOTAL	INV_TAX	INV_TOTAL

LINE TABLE:

INV_NUMBER	LINE_NUMBER	P_CODE	LINE_UNITS	LINE_PRICE	LINE_TOTAL

EMPLOYEE TABLE:

EMP_NUM	EMP_TITLE	EMP_LNAME	EMP_FNAME	EMP_INITIAL	EMP_DOB	EMP_HIRE_DATE	EMP_AREACODE	EMP_PHONE	EMP_MGR

Answer 1

1. Show the details of the vendors who are located in area code 615.

VENDOR TABLE DATA

1	ABC	9999999999	610	0222290750	GUJARAT	200
2	XYZ	8888888888	615	0283224466	GUJARAT	615
3	PQR	7777777777	615	0283224466	GUJARAT	615

QUERY:

SELECT * FROM VENDOR_MASTER WHERE V_AREACODE = '615'

ANSWER:

2 XYZ   8888888888 615   0283224466 GUJARAT     615

3 PQR   7777777777 615   0283224466 GUJARAT     615

2. Show the details of the products that do not have a value for the attribute v_code.

PRODUCT TABLE

P_CODE	P_DESC
1	PRODUCT IS GOOD	2019-10-02	100.00	10.00	10.00	NULL
2	PRODUCT IS VERY HEALTHY	2019-10-03	200.00	5.00	5.00	1
3	PRODUCT IS VERY ENERGETIC	2019-10-03	250.00	5.00	5.00	2

QUERY:

SELECT * FROM PRODUCT_MASTER WHERE V_CODE IS NULL

1 PRODUCT IS GOOD   2019-10-02 100.00      10.00 10.00 NULL

3. Show the details of the invoices whose subtotal is greater than 25 but less than 75.

1	1	2019-02-10	15.00	10.00	25.00
2	2	2019-03-10	28.00	10.00	38.00
3	3	2019-03-10	65.00	10.00	75.00
4	4	2019-03-10	10.00	10.00	20.00

QUERY:

YOU CAN USE BOTH QUERY

SELECT * FROM INVOICE_MASTER WHERE INV_SUBTOTAL BETWEEN 25 AND 75

SELECT * FROM INVOICE_MASTER WHERE INV_SUBTOTAL >= 25 AND INV_SUBTOTAL <= 75

2     2     2019-03-10 28.00 10.00 38.00

3     3     2019-03-10 65.00 10.00 75.00

4. Show the details of the invoice who has the minimum subtotal.

1	1	2019-02-10	15.00	10.00	25.00
2	2	2019-03-10	28.00	10.00	38.00
3	3	2019-03-10	65.00	10.00	75.00
4	4	2019-03-10	10.00	10.00	20.00

QUERY:

SELECT MIN(INV_SUBTOTAL) FROM INVOICE_MASTER

10.00

5. Show the codes and names of the vendors who supplied products.

QUERY:

SELECT * FROM PRODUCT_MASTER

1      PRODUCT IS GOOD            2019-10-02   100.00 10.00 10.00 NULL

2      PRODUCT IS VERY HEALTHY    2019-10-03   200.00 5.00   5.00   1

3      PRODUCT IS VERY ENERGETIC 2019-10-03   250.00 5.00   5.00   2

SELECT * FROM VENDOR_MASTER

1     ABC   9999999999 610   0222290750 GUJARAT     200

2     XYZ   8888888888 615   0283224466 GUJARAT     615

3     PQR   7777777777 615   0283224466 GUJARAT     615

SELECT V.V_CODE,V.V_NAME FROM VENDOR_MASTER V INNER JOIN PRODUCT_MASTER P ON V.V_CODE = P.V_CODE

1     ABC

2     XYZ

6. Using EXCEPT show the codes of the vendors who did not supply any products.

QUERY:

SELECT V.V_CODE FROM VENDOR_MASTER V LEFT JOIN PRODUCT_MASTER P ON V.V_CODE = P.V_CODE

EXCEPT

SELECT V.V_CODE FROM VENDOR_MASTER V RIGHT JOIN PRODUCT_MASTER P

   ON V.V_CODE = P.V_CODE

V_CODE

3

7. Using ‘NOT IN’ show the codes and names of the vendors who did not supply any products.

QUERY:

SELECT V.V_CODE,V.V_NAME FROM VENDOR_MASTER V INNER JOIN PRODUCT_MASTER P ON V.V_CODE = P.V_CODE

WHERE V.V_CODE NOT IN (SELECT VS.V_CODE FROM VENDOR_MASTER VS WHERE VS.V_CODE IS NULL)

1     ABC

2     XYZ

8. List the codes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.

QUERY:

SELECT V.V_CODE, V.V_NAME, COUNT(*) AS TOTAL FROM VENDOR_MASTER V INNER JOIN PRODUCT_MASTER P ON V.V_CODE = P.V_CODE GROUP BY V.V_CODE

V_CODE    TOTAL

1           1

2           1

9. List the names and codes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.

QUERY:

SELECT V.V_CODE, V.V_NAME FROM VENDOR_MASTER V INNER JOIN PRODUCT_MASTER P ON V.V_CODE = P.V_CODE

V_CODE     V_NAME

1           ABC

2           XYZ

10. Add a new attribute (field) status varchar(6) to the EMP table.

STATUS      varchar(6)

11. Update status for employee '100' to 'Temporary'.

QUERY:

UPDATE EMPLOYEE SET STATUS = 'Temporary' WHERE STATUS = '100'

GET ERROR:

MORE THEN 6 CHARACTER

String or binary data would be truncated.

The statement has been terminated.

12. Using inner join, list the details of the products whose line price is greater than 100.

QUERY:

SELECT P.P_CODE FROM PRODUCT_MASTER P INNER JOIN LINE L ON P.P_CODE = L.P_CODE WHERE L.LINE_PRICE >= '100.00'

IF ANY KIND OF SUGGESTION SO MENTION IN COMMENT