Use the following table to answer the questions below: Table name: Customers Column Name Role Data Type Length Constraint customerID stores the ID of a Char 5 Primary key customer custFName stores the first name of a Varchar2 35 Cannot be null customer custLName Stores the last name of a Varchar2 35 Cannot be null customer custGender Store the gender of a Char 1 It should be M customer or 'F' custDOB Stores the date of birth of Date a customer custIncome Store the income of a Number customer a. Write in SQL a command that creates the table Customers according to the above description b. Write in SQL a statement that adds a new column named accountID of type Char(8). This column should be defined as a foreign key that relates the table Customers to the table Account c. Write in SQL a command that deletes the customers whose date of birth is after 22-Dec-1990 d. Write a SQL query that displays the number of customers who born on the 16th day of a month e. Write in SQL a query that displays the ids of the customers who have the highest income f. Create a view named 'customerDetails' to hold all the details of customers whose first names start with 'S' and their incomes are between 500 and 1000 Omani Rials

Use the following table to answer the questions below: Table name: Customers Column Name Role Data Type Length Constraint customerID stores the ID of a Char 5 Primary key customer custFName stores the first name of a Varchar2 35 Cannot be null customer custLName Stores the last name of a Varchar2 35 Cannot be null customer custGender Store the gender of a Char 1 It should be M customer or 'F' custDOB Stores the date of birth of Date a customer custIncome Store the income of a Number customer a. Write in SQL a command that creates the table Customers according to the above description b. Write in SQL a statement that adds a new column named accountID of type Char(8). This column should be defined as a foreign key that relates the table Customers to the table Account c. Write in SQL a command that deletes the customers whose date of birth is after 22-Dec-1990 d. Write a SQL query that displays the number of customers who born on the 16th day of a month e. Write in SQL a query that displays the ids of the customers who have the highest income f. Create a view named 'customerDetails' to hold all the details of customers whose first names start with 'S' and their incomes are between 500 and 1000 Omani Rials

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter9: Joining Data From Multiple Tables

Section: Chapter Questions

Problem 7MC

See similar textbooks

Similar questions

A new table has been requested to support tracking automated emails sent to customers. Create the table and add data as described below. Tablename: email_log Columns: emailid (numeric), emaildate (datetime), customer# (numeric) Primary key: emailid column, define as an Identity Column Add the following data rows and display resulting rows (if any errors occur, explain why the error is expected) Emaildate = current date, customer# = 1007 Emailid = specify to use the column default value, emaildate = current date, customer# = 1008 Emailid = 25, emaildate = current date, customer# = 1009
Which of the following statements retrieves data from the Sales table and stores it in the CompanyDataSet? Me.CompanyDataSet.Fill(Me.Sales) Me.SalesBindingSource.Fill(Me.CompanyDataSet) Me.SalesBindingNavigator.Fill(Me.CompanyDataSet.Sales) Me.SalesTableAdapter.Fill(Me.CompanyDataSet.Sales)
Write, but do not execute, the commands to grant the following privileges: a. User Ashton must be able to retrieve data from the ITEM table. b. Users Kelly and Morgan must be able to add new orders and order lines. c. User James must be able to change the price for all items. d. User Danielson must be able to delete customers. e. All users must be able to retrieve each customers number, name, street, city, state,and postal code. f. f User Pere7 must be able to create an index on the ORDFRS table. g. User Washington must be able to change the structure of the ITEM table. h. User Grinstead must have all privileges on the ORDERS table.
Based on the following table, please find the customers who locate in the same state. sales.customers *customer_id first_name last_name phone email street city state zip_code . Based on the following table, please display the staff and their corresponding manager in the same store. Please display staff’s full name in one column, manager’s full name in another column, and store_id. sales.staffs *staff_id first_name last_name email phone active store_id manager_id
8. Create a trigger for the Invoices table that automatically inserts the vendor name and address for a paid invoice into a table named ShippingLabels. The trigger should fire any time the PaymentTotal column of the Invoices table is updated. The structure of the ShippingLabels table is as follows: CREATE TABLE ShippingLabels (VendorName varchar(50), VendorAddress1 varchar(50), VendorAddress2 varchar(50), VendorCity VendorState VendorZipCode varchar(50), char(2), varchar(20)); Use this UPDATE statement to test the trigger: UPDATE Invoices SET PaymentTotal = 67.92, PaymentDate = '2020-02-23' WHERE InvoiceID = 100;
Use the following table to answer the questions below: Table name: Customers Column Name Role Data Type Length Constraint customerID stores the ID of a customer Char 5 Primary key custFName stores the first name of a customer Varchar2 35 Cannot be null custLName Stores the last name of a customer Varchar2 35 Cannot be null custGender Store the gender of a customer Char 1 It should be ‘M’ or ‘F’ custDOB Stores the date of birth of a customer Date custIncome Store the income of a customer Number 4 Write in SQL a command that creates the table Customers according to the above description (2 marks). Write in SQL a statement that adds a new column named accountID of type Char(8). This column should be defined as a foreign key that relates the table Customers to the table Account (1.5 marks). Write in SQL a command that deletes the customers whose date of birth is after 22-Dec-1990 (1.5 Marks).…
Which attribute may return NULL? %FOUND %ISOPEN %NOTFOUND %ROWCOUNT
Use the following table to answer the question below: Table name: Customers Column Name Role Data Type Length Constraint customerID stores the ID of a customer Char 5 Primary key custFName stores the first name of a customer Varchar2 35 Cannot be null custLName Stores the last name of a customer Varchar2 35 Cannot be null custGender Store the gender of a customer Char 1 It should be ‘M’ or ‘F’ custDOB Stores the date of birth of a customer Date custIncome Store the income of a customer Number 4 Write a SQL query that displays the number of customers who born on the 16th day of a month . Write in SQL a query that displays the ids of the customers who have the highest income . Create a view named ‘customerDetails’ to hold all the details of customers whose first names start with ‘S’ and their incomes are between 500 and 1000 Omani Rials .
Below is part of a Student database. The primary keys are highlighted in bold. Student (studNo, studName, address, mobileNo) Registration (studNo, courseNo, regDate, semester, session) Course (courseNo, courseName, creditHour, level) Project (projNo, projName, courseNo) Assignment (projectNo, studNo, startDate, dueDate, hoursSpent) INSERT INTO STUDENT VALUES (175,'Ali Ahmad','10 jalan Bukit Bintang','019-123');INSERT INTO STUDENT VALUES (176,'Hanna Syamil','32A Jalan Danau Kota','012-234');INSERT INTO STUDENT VALUES (182,'Ibnu Hassan','19 Jalan Kota Raya','011-345');INSERT INTO STUDENT VALUES (183,'Aliya Hamidi','233 Jalan Matahari ','013-456');INSERT INTO STUDENT VALUES (184,'Kayla Adila','98 Jalan Enau','018-567'); INSERT INTO REGISTRATION VALUES (175,1100,'5/SEP/19',1,'2019/2020');INSERT INTO REGISTRATION VALUES (175,1103,'3/SEP/19',2,'2019/2020');INSERT INTO REGISTRATION VALUES (176,1103,'7/SEP/19',2,'2019/2020');INSERT INTO REGISTRATION VALUES…
Some rows of a User table are shown below: User ucode name phone scode 7 Alex 847 - 3902 UX 8 Tony 203 - 3902 PX 9 Charles BD 11 Mary 877 - 3333 BD Which of the following queries retrieves the rows where there is no phone? a. SELECT * FROM User WHERE phone IS NULL b. SELECT * FROM User WHERE phone = “ ” c. SELECT * FROM User WHERE phone NOT IS NULL d. SELECT * FROM User WHERE phone > 0
What will be the result of the following queries: a) select S.stName, R.crsId, R.grade from student S, registration R where S.stId = R.stdId and R.grade=’A’ b) select stId, stCity, crsId from student S right outer join registration R on S.stdId = R.stID; c) select stId, stCity, crsId from student S left outer join Registration C on S.stdId = R.stID;
2- selects the order line items (orderNumber, orderlinenumber) fromthe “orderdetails” table. Also, display the subtotal (quantityOrdered * priceEach) for eachline item and sorts the result set based on the subtotal descending .3- Display unique last names from employees table ordered by last name.4- Display customerNumber, customerName and creditLimit for getting the top five customerswho have the highest creditLimit.5- Find customer Name, customer country, sales representative employee number fromcustomers who do not have a sales representative.6- For each order status, display status and count of orders have this status.7- For Each order number in order details table, Display order number, sum of quantityordered (itemsCount) and sum of (priceEach * quantityOrdered) as total. With condition“total” greater than 1000.8- Display product code, product name and text description for product.9- Find customer number, customer name, order status and order number, for customerwith their orders…