SQL Practice for Autonomous University
Translated by
ScribdTranslationsSQL Practice for Autonomous University
Translated by
ScribdTranslationsAutonomous University of Santo Domingo
Name:
=====
Enrollment:
======
Section:
====
Master:
=========
Subject:
==========
Work:
===========
AUTONOMOUS UNIVERSITY OF SANTO DOMINGO
FACULTY OF SCIENCES
DEPARTMENT OF COMPUTER SCIENCE
Practice #1 of SQL
Prepared by: Mtra. Romery Alberto M.
Given the following tables:
Client
Customer Code Client Name
1 Juan Pérez Active
2 Julio Paz Active
3 Rosa Fernández Active
4 Luis Roja Active
5 Carmen García Active
6 Roberto Ledesma Active
7 Carlos Caraballo Active
8 Juana Rosario Active
9 Pedro Jiménez Active
Invoice
Code Number Amount
Client Invoice
7 120 1,000.00
8 121 500.00
7 122 200.00
5 111 700.00
5 112 1,500.00
9 172 2,000.00
7 173 2,500.00
8 123 3,500.00
9 175 4,600.00
Receipt
Code Number Number Amount
Client Receipt Invoice
7 71 120 -100.00
7 80 120 -200.00
8 82 121 -100.00
5 91 111 -200.00
5 93 112 -300.00
5 96 112 -400.00
9 98 172 -200.00
7 99 173 -500
8 100 123 -3,500
9 101 175 -4,000
7 102 173 -600
7 103 122 -100
Carry out the following commands:
1) Create the database: PRASQL
2) Create the tables: Customer, Invoice and Receipt
3) Create the PRIMARY KEY of the tables: Client, Invoice, and Receipt
4) Create the FOREIGN KEYs for the tables: Invoice and Receipt
5) Make the necessary 'SELECT' statements to find:
[Link] amount billed by Client.
Total amount paid for each client invoice
[Link] who have more than one invoice.
[Link] invoices of Client number 5
The receipts paid by Customer number 16
[Link] amount by customer.
[Link] of receipts per client.
h. Total number of invoices
[Link] number of receipts
[Link] with the highest billed amount.
[Link] with the lowest billed amount.
The outstanding debt of the customer for each invoice
The total debt of the client
The total amount paid by the customer on each invoice
The total amount paid by the customer
6) Given the following tables, do the following:
Invoice
Code No.
Client Invoice Amount
7 120 1,000.00
8 121 500.00
7 122 200.00
5 111 700.00
5 112 1,500.00
9 172 2,000.00
7) Inquiry of invoices for client 5 ordered by invoice number
Add the following records to the invoices table
Invoice
Code No.
Client Invoice Amount
5 110 1,300.00
5 113 2,500.00
7 114 8,200.00
7 115 5,700.00
9 116 4,500.00
9 117 2,600.00
9 118 3,500.00
9) Delete the following records in the invoice table
Invoice
Code No.
Client Invoice Amount
5 110 1,300.00
9 118 3,500.00
10) Update the amount of invoice 114 to 34,000
Invoice
Code No.
Client Invoice Amount
7 114 8,200.00
Note: You have to use the nomenclature I taught you to name tables and fields.
Code:
CREATE SCHEMA `pra_sql` DEFAULT CHARACTER SET utf8;
use pra_sql;
CREATE TABLE `client` (
Client_Code INT unique
`customer_name` VARCHAR(45) ,
state
PRIMARY KEY (`Customer_Code`)
);
CREATE TABLE invoice (
invoice_number int unique
amount varchar(45)
Client_Code
primary key (`invoice_number`),
FOREIGN KEY (`Customer_Code`) REFERENCES client(`Customer_Code`)
);
CREATE TABLE receipt (
receipt_number
invoice_number
Client_Code
amount int,
primary key (`receipt_number`),
FOREIGN KEY (`invoice_number`) REFERENCES invoice(`invoice_number`),
FOREIGN KEY (`Client_Code`) REFERENCES client(`Client_Code`)
);
select * from pra_sql.client;
INSERT INTO client(`Client_Code`,`client_name`,`status`)
VALUES (1,"Juan Perez","Activo");
INSERT INTO client(`Client_Code`,`client_name`,`status`)
VALUES (2,"Julio Paz","Activo");
INSERT INTO client(`Client_Code`,`client_name`,`status`)
VALUES (3,"Rosa Fernández","Activo");
INSERT INTO client(`Client_Code`, `client_name`, `status`)
VALUES (4,"Luis roja","Activo");
INSERT INTO client(`Client_Code`,`client_name`,`status`)
VALUES (5,"Carmen García","Activo");
INSERT INTO client(`Client_Code`,`client_name`,`status`)
VALUES (6,"Roberto Ledesma","Activo");
INSERT INTO client(`Client_Code`, `client_name`, `status`)
VALUES (7,"Carlos Caraballo","Activo");
INSERT INTO customer(`Customer_Code`, `customer_name`, `status`)
VALUES (8,"Juana Rosario","Activo");
INSERT INTO client(`Client_CodeClient_CodeClient_CodeClient_Code`,`client_name`,`status`)
VALUES (9,"Pedro Jimenez","Activo");
select * from client;
INSERT INTO invoice(`invoice_number`,`amount`,`Customer_Code`)
VALUES (120, 1000.00, 7);
INSERT INTO invoice(`invoice_number`,`amount`,`Client_Code`)
VALUES (121,500.00,8);
INSERT INTO invoice(`invoice_number`,`amount`,`Client_Code`)
VALUES (122, 200.00, 7);
INSERT INTO invoice(`invoice_number`,`amount`,`Customer_Code`)
VALUES (111,700.00,5);
INSERT INTO invoice(`invoice_number`,`amount`,`Client_Code`)
VALUES (112,1500.00,5);
INSERT INTO invoice(`invoice_number`,`amount`,`Client_Code`)
VALUES (172, 2000.00, 9);
INSERT INTO invoice(`invoice_number`,`amount`,`Client_Code`)
VALUES (173,2500.00,7);
INSERT INTO invoice(`invoice_number`,`amount`,`Customer_Code`)
VALUES (123,3500.00,8);
INSERT INTO invoice(`invoice_number`, `amount`, `Client_Code`)
VALUES (175, 4600.00, 9);
select * from invoice;
INSERT INTO receipt(`receipt_number`, `invoice_number`, `Client_Code`, `amount`)
VALUES (71,120,7,-100.00);
INSERT INTO receipt(`receipt_number`,`invoice_number`,`Client_Code`,`amount`)
VALUES (80,120,7,-200.00);
INSERT INTO receipt(`receipt_number`,`invoice_number`,`Client_Code`,`amount`)
VALUES (82,121,8,-100.00);
INSERT INTO receipt(`receipt_number`,`invoice_number`,`Client_Code`,`amount`)
VALUES (91,111,5,-100.00);
INSERT INTO receipt(`receipt_number`,`invoice_number`,`Client_Code`,`amount`)
VALUES (93, 112, 5, -300.00);
INSERT INTO receipt(`receipt_number`, `invoice_number`, `Client_Code`, `amount`)
VALUES (96,112,5,-400.00);
INSERT INTO receipt(`receipt_number`,`invoice_number`,`Client_Code`,`amount`)
VALUES (98,172,9,-200.00);
INSERT INTO receipt(`receipt_number`,`invoice_number`,`Client_Code`,`amount`)
VALUES (99,173,7,-500.00);
INSERT INTO receipt(`receipt_number`,`invoice_number`,`Client_Code`,`amount`)
VALUES (100,123,8,-3500.00);
INSERT INTO receipt(`receipt_number`,`invoice_number`,`Client_Code`,`amount`)
VALUES (101,175,9,-4000.00);
INSERT INTO receipt(`receipt_number`, `invoice_number`, `Client_Code`, `amount`)
VALUES (102,173,7,-600.00);
INSERT INTO receipt(`receipt_number`,`invoice_number`,`Client_Code`,`amount`)
VALUES (103,122,7,-100.00);
select * from receipt;
select SUM(amount) from invoice;
SELECT
t1.invoice_number,
sum([Link])
Client_Code
FROM
invoice t1
INNER JOIN client t2
ON t1.Client_Code = t2.Client_Code;
SELECT
Client_Code
t1.client_name,
[Link]
FROM
client t1
INNER JOIN invoice t2
ON t1.Client_Code = t2.Client_Code;
SELECT
t1.Client_Code
t1.customer_name
[Link]
t2.invoice_number
[Link]
FROM
client t1
INNER JOIN invoice t2
ON t1.Client_Code = 5;
SELECT
t1.Client_Code
t1.customer_names
[Link],
count(t2.invoice_number),
[Link]
FROM
client t1
INNER JOIN invoice t2
ON t1.Client_Code = t2.Client_Code
select max(amount), Client_Code from invoice;
select min(amount), Client_Code from invoice;
select sum(amount) from receipt
where Client_Code = 7;
INSERT INTO invoice(`invoice_number`,`amount`,`Client_Code`)
VALUES (110, 1300.00, 5);
INSERT INTO invoice(`invoice_number`,`amount`,`Client_Code`)
VALUES (113,2500.00,5);
INSERT INTO invoice(`invoice_number`, `amount`, `Customer_Code`)
VALUES (114,8200.00,7);
INSERT INTO invoice(`invoice_number`,`amount`,`Client_Code`)
VALUES (115,5700.00,7);
INSERT INTO invoice(`invoice_number`,`amount`,`Client_Code`)
VALUES (116,4500.00,9);
INSERT INTO invoice(`invoice_number`,`amount`,`Client_Code`)
VALUES (117,2600.00,9);
INSERT INTO invoice(`invoice_number`,`amount`,`Client_Code`)
VALUES (118,3500.00,9);
DELETE FROM invoice
WHERE Client_Code = 5 and invoice_number = 110;
DELETE FROM invoice
WHERE Client_Code = 9 and invoice_number = 118;
UPDATE invoice
SET amount = '34000'
WHERE Client_Code=7 and invoice_number=114;