Database Concepts and SQL Questions

1- In an SQL statement, which of the following parts states the conditions for row selection? a) SELECT b) FROM c) WHERE d) GROUP BY 2- What command is used to get back the privileges offered by the GRANT command? a) Grant b) Revok...

1- In an SQL statement, which of the following parts states the conditions for row selection? a) SELECT b) FROM c) WHERE d) GROUP BY 2- What command is used to get back the privileges offered by the GRANT command? a) Grant b) Revoke c) Execute d) Run 3- Language for defining the database structure a) DML b) DLL c) DSL d) DDL 4- Avg, min, max, sum and count are called functions. a) algebra b) normal c) aggregate d) complex 5- keys are identifiers that enable a dependent relation. a) Primary Keys b) Secondary Keys c) Alternate Keys d) Foreign Keys VARCHAR AND CHAR Q. What are the differences between Stored Procedure and Trigger VARCHAR is variable length while CHAR is fixed length Triggers It can execute automatically Stored procedures execute in response to a database event (INSERT, UPDATE, or DELETE) Q: Write the SQL commands for the following: 1- Retrieve all products Name, Unit Price, and Quantity whose product ID is 42; SELECT P.ProductName , O.UnitPrice , O.Unantity FROM PRODUCTS P , ORDER DETAILS O WHERE P.ProductID = O.ProductID AND ProductID = 42 ; 2- Retrieve the Customer ID who has the minimum Quantity SELECT CustomerID FROM ORDERS O , ORDER DETAILS D WHERE O.OrderID = D.OrderID AND Quantity = (SELECT MIN(Quantity) FROM ORDER DETAILS) ; 3- Retrieve the minimum Quantity SELECT MIN(Quantity) FROM ORDER ;  4- UPDATE UnitPrice to 23 For Product ID is 11 UPDATE ORDER DETAILS SET UnitPrice = 23 WHERE ProductID = 11 ; 5- Add product ID is 80 , And Product Name can be null INSERT INTO PRODUCTS (ProductID , ProductName) VALUES ( 80 , ‘Null’) ; Q) What foreign key update action should be included when defining the tables to ensure that changes to the primary key value in one table are reflected on all referencing tables? On Update Casecad  DEPARTMENT Deptno Deptname Total_number_ Total_employee_ of_employee salary 1 Personnel 2 100,000 2 Accounting 2 200,000 3 Publication 3 140,000 4 Marketing 3 150,000 5 Information Technology 2 250,000 6 Customer Service 2 100,000 EVENT Eventno Eventname Location Eventdate Deptno E1 Open Day Exhibition Hall 01/02/20 4 E2 IT for Girls Seminar Room 05/06/20 5 E3 Superannuation Info Seminar Room 07/08/20 2 Session E4 SAP Short Course Laboratory 08/06/20 5 Based on the above tables, write a stored procedure which receives a department number (Deptno) as input, and displays the list of events to be held by that department? CREATE OR REPLACE PROCEDURE showInfoDepart(p_Dept event.dept_No%TYPE) AS CURSOR showDept IS SELECT Event_Number , Event_Name , Location from event where dept_No=p_Dept; BEGIN FOR v_cursor IN showDept LOOP dbms_output.put_line(v_cursor.Event_Number||' '|| v_cursor.Event_Name||' '|| v_cursor.Location); END LOOP; END showInfoDepart; Find the Error Q1) CustomerID is primary key : INSERT INTO ORDERS (CustomerID) VALUES ( DUMON ) ; a) The OrderID will update b) Duplicate( Primary Keys Constraints ) c) Syntax Error d) The data will add Q2) OrderID is Foreign Keys INSERT INTO ORDERS (ORDERID) VALUES ( 001) ; a) Referential Integrity b) Duplicate (Primary Keys Constraints) c) The data will add d) Foreign key match primary key