QUESTION 1 [19] 1.1 Write an SQL statement to create a table named riverview_customers, using a subquery based on table f_customers, to include the columns as displayed in the results listed below. (4) SQL> SELECT * 2 FROM riverview_customers; CUST# CUSTOMER JOINDATE ---------- -------------------------- --------- 10006 Johnny Boy Radingoana 17-JUL-18 10008 Gentleman Baloy 13-NOV-18 10012 Lazarus Mashaba 15-APR-19 10015 Louis Malherbe 23-SEP-19 10024 Liberty Nkosi 15-JAN-20 10026 Susan Xaba 01-FEB-20 6 rows selected. 1.2 Write an SQL statement to create an alternative name rivcusts to be used for table riverview_customers. (1.5) 1.3 Without re-creating the table of question 1.1, write one SQL statement to answer this question. Change the definition of table riverview_customers to include the customer type, but name this column custtype. Put a restriction on the table to ensure that the customer types will include values of O, L and N only. Name this restriction valid_custtypes. (4) 1.4 Write one SQL statement to create a comment that refers to the custtype column of the previous question. The comment must read “Only O, L, and N customer types are allowed!” (2) 1.5 Write an SQL statement to create a sequence named rcust_seq to start at 10100 and end at 10200. When the sequence has reached 10200, it must restart at 10100. The Oracle Server must allocate 25 values for this sequence in memory. The sequence must increase with two everytime a new sequence value is generated. (2.5) 1.6 Write two SQL statements to first remove the alternative name you created in your answer to question1.2 and then rename table riverview_customers to rivcusts. (2) 1.7 Use the sequence you created in your answer to question 1.5 and write one SQL statement to add a new record to table rivcusts. The customer number must be the next available number according to the sequence. The customer type is L and the customer (Johnyboy Mahlangu) joined FoneForFood on the current date (use a system variable for the current date). You are not allowed to name the columns.
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
QUESTION 1 [19]
1.1 Write an SQL statement to create a table named riverview_customers, using a subquery based on table f_customers, to include the columns as displayed in the results listed below. (4)
SQL> SELECT *
2 FROM riverview_customers;
CUST# CUSTOMER JOINDATE
---------- -------------------------- ---------
10006 Johnny Boy Radingoana 17-JUL-18
10008 Gentleman Baloy 13-NOV-18
10012 Lazarus Mashaba 15-APR-19
10015 Louis Malherbe 23-SEP-19
10024 Liberty Nkosi 15-JAN-20
10026 Susan Xaba 01-FEB-20
6 rows selected.
1.2 Write an SQL statement to create an alternative name rivcusts to be used for table riverview_customers. (1.5)
1.3 Without re-creating the table of question 1.1, write one SQL statement to answer this question. Change the definition of table riverview_customers to include the customer type, but name this column custtype. Put a restriction on the table to ensure that the customer types will include values of O, L and N only. Name this restriction valid_custtypes. (4)
1.4 Write one SQL statement to create a comment that refers to the custtype column of the previous question. The comment must read
“Only O, L, and N customer types are allowed!”
(2)
1.5 Write an SQL statement to create a sequence named rcust_seq to start at 10100 and end at 10200. When the sequence has reached 10200, it must restart at 10100. The Oracle Server must allocate 25 values for this sequence in memory. The sequence must increase with two everytime a new sequence value is generated. (2.5)
1.6 Write two SQL statements to first remove the alternative name you created in your answer to question1.2 and then rename table riverview_customers to rivcusts. (2)
1.7 Use the sequence you created in your answer to question 1.5 and write one SQL statement to add a new record to table rivcusts. The customer number must be the next available number according to the sequence. The customer type is L and the customer (Johnyboy Mahlangu) joined FoneForFood on the current date (use a system variable for the current date). You are not allowed to name the columns.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
