Databases Query Assignment -

Suoritettu Julkaistu Mar 21, 2016 Maksettu toimituksen yhteydessä
Suoritettu Maksettu toimituksen yhteydessä

workshop instructions

Write the SQL command to create the customer and the invoice tables using the InnoDB database engine. Define the PK for each table and a FK constraint on CUS_CODE of the invoice table.

Run the following query: select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE and show the output.

Write the SQL command to insert a record in the invoice table with a CUS_CODE that does not exist in the customer table. Show the output message returned by mysql.

Drop the FK constraint on CUS_CODE of the invoice table and redefine it with “on HYPERLINK "[login to view URL]"update HYPERLINK "[login to view URL]" HYPERLINK "[login to view URL]"cascade” and “on HYPERLINK "[login to view URL]"delete HYPERLINK "[login to view URL]" HYPERLINK "[login to view URL]"cascade” clauses.

Write sql commands to insert one record in the customer table and one record in the invoice table.

Check how Foreign keys help guarantee referential integrity within the database.

Check the data in both tables and show the output.

Write a sql command to change the CUS_CODE in the customer table to any new value. Show what message do you get from the system.

Check the data in both tables and show the output.

Write a sql command to delete all rows from the customer table. Show what message do you get from the system.

Check the data in both tables and show the output.

Drop the FK constraint on CUS_CODE of the invoice table and redefine it with “on HYPERLINK "[login to view URL]"update HYPERLINK "[login to view URL]" HYPERLINK "[login to view URL]"restrict” and “on HYPERLINK "[login to view URL]"delete HYPERLINK "[login to view URL]" HYPERLINK "[login to view URL]"restrict” clauses.

Check how Foreign keys help guarantee referential integrity within the database.

Write a sql command to update the CUS_CODE of any record in the customer table. What message do you get?

Write a sql command to delete the records of the customer table. What message do you get?

Load the sample data of your assignment for the customer and the invoice tables.

Write the SQL query to get the product of the two tables. To get the product of two tables T1 and T2, run the following query: select * from T1, T2. Show the output.

Write the SQL query to get the list of all customers and their corresponding invoices when available. Use left Join.

As you do not have the super privilege on your mysql account under zenit to create triggers, Use [login to view URL] or your own installation of mysql. Using [login to view URL], provide a username and password and database name as well. You will need to activate your account by clicking on a link sent to your email while the creation of the account. Once you login, create the customer and the invoice table and create the following triggers.

Create a trigger before insert on the customer table that checks if the balance is different than zero and if so it sets its value to zero. Write the sql command to insert a customer record with a balance different than zero, then check the data in the table.

Create a trigger before insert on the invoice table that sets the value of the invoice total as the sum of invoice subtotal and invoice tax. Write the sql command to insert an invoice record without providing a value for the invoice total, then check the data in the table.

more info in attached file.

Tietokantakehitys Tietokantaohjelmointi MySQL SQL

Projektin tunnus: #10009700

Tietoa projektista

9 ehdotusta Etäprojekti Aktiivinen Mar 22, 2016

Myönnetty käyttäjälle:

nyarOnyari

Hello, I have read your project description about Database Design and I would appreciate if you can open discussion with me and I see how we can work together. Am a graduate in Computer Science and have already designe Lisää

$30 CAD 2 päivässä
(6 Arvostelua)
3.5

9 freelanceria on tarjonnut keskimäärin $37 tähän työhön

FASTGuru

Hi Sir! I'm the expert developer. with 5 years of experience I assure you the highest quality of the work. Once you have awarded me your project, I am available for any question. and you'll not have to be worried about Lisää

$54 CAD 1 päivässä
(64 arvostelua)
6.7
ChiragLathiya

message me before awarding this project to me message me before awarding this project to me message me before awarding this project to me message me before awarding this project to me message me before awarding thi Lisää

$30 CAD 1 päivässä
(208 arvostelua)
5.5
it2051229

Hi, I have read the requirements and I would like to do this project if given the opportunity. Let me know if you are interested so I can have the complete details. Thanks.

$30 CAD 1 päivässä
(121 arvostelua)
5.7
ashupathania

I have 4 + years of experience in MSBI Technology (SQl Server 2008/2012, SSIS, SSRS, and SSAS) Worked with the reputed clients like Microsoft, HCL, and Dicks Sporting Goods etc. Expert in creating Sql queries ,Dynam Lisää

$70 CAD 3 päivässä
(26 arvostelua)
4.8
phiona06

Hi! I'm senior lecturer on databases and I've more 10 years experience on DBMS, including MySQL. As I understood, you need help to your university task. I already helped some students with queries in this site. So, I t Lisää

$30 CAD 1 päivässä
(29 arvostelua)
4.7
riveralf

Hello, I am an experienced database administrator and SQL developer... I assure you that I can fulfill your requirement with high quality.

$25 CAD 1 päivässä
(9 arvostelua)
3.3
ENRICOJ

HI. I WOULD BE HAPPY TO ASSIST YOU AS I HAVE MUCH EXPERIENCE IN SQL AND DATABASE DESIGN. PLEASE FEEL FREE TO APPROACH ME FOR FURTHER ASSISTANCE. KIND REGARDS ENRICO

$35 CAD 1 päivässä
(4 arvostelua)
2.1
kibroma

Even though I am new here I have great experience of software development. All of my experience over the past years I am sure I will develop it well and deliver on time.

$25 CAD 1 päivässä
(0 arvostelua)
0.0