Scribd
Upload
4 views7 pages

SQL Database Design for Small Store

The document describes the steps to create a database called "store" to store information about customers, products, and sales for a small stationery business. It includes SQL scripts to create the tables, add sample data to the customer and product tables, and conduct some sales with their details. It also includes scripts to delete, modify, and query data.

Translated by

ScribdTranslations
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views7 pages

SQL Database Design for Small Store

The document describes the steps to create a database called "store" to store information about customers, products, and sales for a small stationery business. It includes SQL scripts to create the tables, add sample data to the customer and product tables, and conduct some sales with their details. It also includes scripts to delete, modify, and query data.

Translated by

ScribdTranslations
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

DIRECTED SQL PRACTICE.

Create a database named 'store' that is related to the activity of a


small business that sells items (stationery) to customers.

The company must store information about each of its customers.

The company must store information about the items it sells.

The business must store data on the sales it makes.

All sales generate a ticket or sales receipt with its corresponding detail that
describe the product that is sold, the quantity, the price, and the amount of the product
price X quantity.

1. Create store database.

create database store;

2. Suggested DB design.
3. SQL code to create tables with their corresponding relationships between the tables
that they require it:
Table with primary key rfc
b. products: Table with primary key product_key
c. sales: Table with primary key no_sale and foreign key rfc
Sales details: Table WITHOUT primary key and two foreign keys: no_sale and
product_key.

4. SQL script to add 10 records to clients. [10 inserts].


Only 3 records are displayed for practice purposes.

delete from clients;

insert into clients (rfc, last_name, maternal_last_name, first_name,


street_no
values ('LALO101010', 'LARES', 'LOPEZ', 'OSVALO')
November 20, 2010
6181345678

insert into clients (rfc, last_name, mother_last_name, first_name,


street_no
values ('PACO101010', 'PAEZ', 'CORDOVA', 'OSCAR',
FELIPE PESCADOR

insert into clients (rfc, last name, mother's last name, first name,
street_no
values ('GALO101010', 'GARCIA', 'LARES', 'OLGA')
NEGRETE 1005 NTE.

select * from clients;


5. SQL script to add 10 products [10 insert].
Only 9 records are displayed for practice purposes.

delete from products;

insert into products (product_key, description, cost)


values ('LIB100X', 'LIBRETA MARCA X', 56.70);

insert into products (product_key, description, cost)


values ('PLU100X', 'PLUMA MARCA X', 5.00);

insert into products (product_key, description, cost)


values ('GOMAX', 'GOMA BRAND X', 2);

insert into products (product_key, description, cost)


values ('LIB100Y', 'LIBRETA MARCA Y', 76.90);

insert into products (product_key, description, cost)


PLU100Y

insert into products (product_key, description, cost)


GOMAY

insert into products (product_key, description, cost)


LIB100Z

insert into products (product_key, description, cost)


values ('PLU100Z', 'PLUMA MARCA Z', 7.60);

insert into products (product_key, description, cost)


GOMAZ

select * from products;

6. SQL script to add 5 sales including their details. [5 inserts]


Note: only 2 sales including their details are added for practice purposes.

delete from salesdetails;

delete from sales;

First Sale Including its Detail


start transaction;

A new sale is added


The total field value remains pending ...
It should be the sum of all the amounts of it.
sales number.
insert into sales (sale_number, date, rfc)
values (100, '2010-03-04','LALO101010');

The sale number 100 is detailed


2 different products were bought
5 and 10 quantities of each
insert into sales_details (sale_number, product_key,
quantity, price, amount
values (100, 'LIB100X', 5, 65.90, cantidad * precio);
insert into sales_details (sale_number, product_key,
quantity
values (100, 'PLU100Z', 10, 9.90, cantidad * precio);

commit;

Second Sale Including its Detail


start transaction;

A new sale is added


The value for total field remains pending ...
-- it should be the sum of all amounts of that
sales number.
insert into sales (sale_no, date, rfc)
values (101, '2010-06-07','GALO101010');

the sale number 100 is detailed


3 different products were purchased
LINRETA, PEN AND ERASER OF THE BRAND 'Y'
2, 4, and 6 amounts of each one
insert into sales_detail (sale_no, product_key,
quantity, price, amount
values (101, 'LIB100Y', 2, 86.90, cantidad * precio);

insert into sales_detail (sale_number, product_key,


cantidad
values (101, 'PLU100Y', 4, 7.90, cantidad * precio);

insert into sales_details (sale_number, product_key,


quantity, price, amount
values (101, 'GOMAY', 6, 6.50, cantidad * precio);
commit;

-- Notes:
FOR THE RULES OF RELATION OR REFERENTIAL INTEGRITY
It is necessary to capture customers from those that exist in the customers table;
-- Similarly, it is necessary to capture products (the keys) of those
-- products that exist in the products table.

select * from sales;

select * from salesdetails;

Database transactions mean that all instructions are executed or


nothing is done. It serves to ensure that all operations are carried out for
in case there is any error, consistency is maintained in the DB.

7. SQL script to delete records [5 delete]


Note: Only one delete is shown.

Delete data of an existing client:


delete from clients
where rfc = 'ALGO';

8. SQL script to make modifications [5 update]


Note: Only one update is shown.

Modify client data.

update clients
new
where rfc = 'LALO101010';

9. SQL script to make various queries. [10 select]


a. List of all customers
b. List of products
c. The data of a client
d. Cost data of a particular product
e. Those who live in the city of DURANGO
What is the detail of a particular sale, for example, sale 100.
g. List of all sales (WITHOUT DETAIL)
h. List of clients with any last name.
i. List of product data searched by its description.
j. Information about the sale that has been made to a client, for example, to
LALO101010

You might also like