Difficulty A
T13.001- Create a view (VusuAli) of the delegation of Alicante that allows users to see the province of Alicante (code '03').
T13.002- With the previous view, list all users of the province of Alicante sorted by last name and name.
T13.003- With the previous view, list all users in the province of Alicante who are called Carolina or Iloveny, sorted by last name and name.
T13.004- Using the previous view eliminates users from the province of Alicante who are called Carolina or Iloveny.
What happened? Why?
T13.005- Try inserting via VusuAli (email, name, surname, first name, province, town)
('yoyo@gmial.es','99888777R', 'FERNANDEZ FERNANDEZ', 'ROSA', '04', '1225')
What happened? Why?
T13.006- Create a view that informs us of the total amount to pay for each order in OnlineStore and its date. Check that you give us the correct data.
T13.007- Using the previews, list all emails, first and last names of the user of Alicante, and if they have orders also the total paid by the user.
T13.008- Create another view that informs us of the total amount to be paid for each order made by Alicante users and their date (date only).
T13.009- Using the new preview, modify your previous solution and list all emails, user's first and last names, and if they have orders also the total paid by the user.
T13.010- Create in your database a table with the same structure as OnlineStore.uuser. Your table will be called "usuAlm".
CREATE TABLE usuAlm LIKE OnlineStore.uuser
creates a new table (usuAlm) with OnlineStore.uuser columns and primary key, but not foreign keys, if there is any. Those, if you need them, must be defined with ALTER TABLE. The table is empty, it has not rows.
Create a "VusuAlm" view that allows you to manage (registrations, deletions, modifications and queries) Almeria users (code '04') and no other.
T13.011- Try to insert using VusuAlm (email, ID, surname, name, province, town)
('rgg2@gmial.es','11222333R','GOMEZ GOMEZ','ROSA','04','1002')
What happened? Why?
T13.012- Using VusuAlm removes all the possible rows of users that you have and inserts these 2 users into your USER table (email, ID, surname, name, province, town):
('rff20@gmial.es', '11222333R', 'FERNANDEZ FERNANDEZ', 'ROSA', '04', '0530')
('jmm119@gmial.es', '22333444T', 'MARTINEZ MARTINEZ', 'JULIA', '04', '1002')
T13.013- Try to insert using VusuAlm (email, ID, surname, name, province, town)
('tutu@gmial.es','00999000T','LOPEZ DE LOPEZ','ANA','03','1225')
What happened? Why?
T13.014- You are going to build the delegation of Almeria. You already have 2 users (exercise 12), insert some orders for some of them.
In OnlineStore there is no user from that province. The usuAlm table already has it but without a foreign key.
ALTER TABLE usuAlm ADD FOREIGN KEY (town, province) REFERENCES OnlineStore.town (codm, province);
The previous order adds that foreign key. Note that the TOWN table is referenced in OnlineStore. That means that you do not need this table in your database, you will manage it through views.
The other tables you need are all those about what your users buy. You must decide which tables you need in your database and which ones you will only use as a reference from OnlineStore -and you do not need to recreate them-.
T13.015- You have your Almeria users and access to the other OnlineStore users. Of your users, those of Almeria, and all others in OnlineStore, list email, surnames, name, and names of the town and province.
T13.016- On your database, create a view TApedidosAlm showing order number, user, date and total money paid for each order. These will be the orders from Almería delegation.
T13.017- Use VusuAlm and TApedidosAlm to show all users of Almeria (email, name, and surnames) and, if they have orders, order number and total paid for each one of them also.