logo

Desklib - Online Library for Study Material with Solved Assignments, Essays, Dissertation etc

6 Pages703 Words93 Views
   

Added on  2020-03-23

About This Document

Desklib is an online library for study material with solved assignments, essays, dissertation etc. It provides instant access to millions of study resources for various subjects, courses and colleges/universities.

Desklib - Online Library for Study Material with Solved Assignments, Essays, Dissertation etc

   Added on 2020-03-23

ShareRelated Documents
1. Show sums of line totals for each invoice.Query:SELECT `inv_number`, SUM(`line_total`) FROM `line` GROUP BY `inv_number`;Output:2. Locate the record in the vendor table that does not have a value for the attribute V_STATEQuery:SELECT * FROM `vendor` WHERE `v_state` IS NULL;Output:
Desklib - Online Library for Study Material with Solved Assignments, Essays, Dissertation etc_1
3. Find the customers whose balance is greater than 200 but less than 300.Query:SELECT `cus_code`, `cus_lname`, `cus_lname`, `cus_initial` FROM `customer` WHERE`cus_balance` > 200 AND `cus_balance` <300;Output:4. Using natural join and having, list the customers whose invoice total is greater than 200Query:SELECT `cus_code`, CONCAT(`cus_initial` ,' ', `cus_fname` , ' ',`cus_lname`) FROM `customer` NATURAL JOIN `invoice` GROUP BY `cus_code` HAVING SUM(`inv_total`)>200;Output:
Desklib - Online Library for Study Material with Solved Assignments, Essays, Dissertation etc_2
5. Show the names of the customers for whom we have invoices.Query:SELECT `cus_code`, CONCAT(`cus_initial` ,' ', `cus_fname` , ' ',`cus_lname`) AS cus_name FROM `customer` WHERE `cus_code` IN (SELECT `cus_code` FROM `invoice`);Output:6. Using natural join and having, list the customers whose invoice total is greater than the average Query:SELECT `cus_code`, CONCAT(`cus_initial` ,' ', `cus_fname` , ' ',`cus_lname`) AS cus_name FROM `customer` NATURAL JOIN `invoice` GROUP BY `cus_code` HAVING SUM(`inv_total`)>( SELECT AVG(`inv_total`) FROM invoice);Output:
Desklib - Online Library for Study Material with Solved Assignments, Essays, Dissertation etc_3

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Show Sums of Line Totals for Each Invoice
|6
|479
|147

Database Module Assignment
|13
|366
|40

Database Assignment Module
|13
|375
|111

Database Module | Assignment
|13
|332
|92

Show sums of line totals for each invoice.. Query:.
|6
|460
|212

Structured Query Language (SQL) Assignment
|4
|451
|190