SQL problems: Task – Create a transaction that given a invoiceId will delete tha
ID: 3596452 • Letter: S
Question
SQL problems:
Task – Create a transaction that given a invoiceId will delete that invoice (There may be constraints that rely on this, find out how to resolve them).
Task – Create a transaction nested within a stored procedure that inserts a new record in the Customer table.
Task - Create an after insert trigger on the employee table fired after a new record is inserted into the table
Task – Create an after update trigger on the album table that fires after a row is inserted in the table
Task – Create an after delete trigger on the customer table that fires after a row is deleted from the table.
Task – Create an inner join that joins customers and orders and specifies the name of the customer and the invoiceId.
Task -- Create an outer join that joins the customer and invoice table, specifying the CustomerId, firstname, lastname, invoiceId, and total.
Task – Create a right join that joins album and artist specifying artist name and title.
Task – Create a cross join that joins album and artist and sorts by artist name in ascending order.
Task – Perform a self-join on the employee table, joining on the reportsto column.
List of tables and (columns) EMPLOYEE (EMPLOYEEID, LASTNAME, FIRSTNAME, TITLE, REPORTSTO,
BIRTHDATE, HIREDATE, ADDRESS, CITY, STATE, COUNTRY, POSTALCODE, PHONE,
FAX, EMAIL)
INVOICE(INVOICEID,CUSTOMERID,INVOICEDATE)
CUSTOMER(CUSTOMERID, (FIRSTNAME, LASTNAME, ORDER)
ALBUM(ALBUMID, TITLE, ARTISTID)
ARTIST(ARTISTID, NAME)
ALBUM(ALBUMID, TITLE, ARTISTID)
Explanation / Answer
1)
# This transaction will delete a record from INVOICE relation on passing specific invoice ID. After executing this transaction commit has to be executed in order to prevent rollback.
DELETE FROM INVOICE WHERE INVOICEID = ‘invoiceid’;
COMMIT;
2)
# Below query will create a procedure named 'insert_customer', which will insert a new record in relation 'customer'.
Create procedure insert_customer
(
IN c_customer_id int(11),
IN c_first_name varchar(15),
IN c_last_name varchar(15),
IN c_order varchar(10)
)
Begin
Insert into CUSTOMER
(
CUSTOMERID,
FIRSTNAME,
LASTNAME,
ORDER
)
Values
(
c_customer_id,
c_first_name,
c_last_name,
c_order
);
END
GO
3)
# This trigger will be executed on inserting a new record in relation 'employee'.
CREATE TRIGGER insert_on_employee
AFTER INSERT ON EMPLOYEE
BEGIN
SELECT s.CUSTOMERID FROM CUSTOMER AS c;
INSERT INTO INVOICE
SET CUSTOMERID = s.CUSTOMERID;
END;
4)
# This trigger will be executed on updating the ALBUM ID in relation 'album'.
CREATE TRIGGER update_on_album
AFTER UPDATE ON ALBUM
WHEN (new.ALBUMID = ‘AL-101’
BEGIN
SELECT al.ARTISTID FROM ALBUM AS al;
INSERT INTO ARTIST
SET ARTISTID = al.ARTISTID;
END;
5)
# In this trigger on deleting a customer from CUSTOMER relation the corresponding customerid record will be deleted from the invoice relation.
CREATE TRIGGER delete_on_customer
AFTER DELETE ON CUSTOMER
BEGIN
SELECT c.CUSTOMERID FROM CUSTOMER AS c;
DELETE FROM INVOICE
WHERE CUSTOMERID = c.CUSTOMERID;
END;
7)
# Outer join between 'customer' and 'invoice' relation.
SELECT c.CUSTOMERID, c.FIRSTNAME, c.LASTNAME, i.INVOICEID, SUM(amount) AS TOTAL
FROM CUSTOMER AS c OUTER JOIN INVOICE AS i
ON c.CUSTOMERID = i.CUSTOMERID
GROUP BY c.CUSTOMERID, c.FIRSTNAME, c.LASTNAME, i.INVOICEID;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.