Oracle 12c: SQL
3rd Edition
ISBN: 9781305251038
Author: Joan Casteel
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Expert Solution & Answer
Chapter 6, Problem 1HOA
Explanation of Solution
Creating a SEQUENCE:
The
Syntax:
CREATE SEQUENCE sequenceName
[INCREMENT BY value]
[START WITH value]
[{MAXVALUE value | NOMAXVALUE}]
[{MINVALUE value | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{ORDER | NOORDER}]
[{CACHE value | NOCACHE}];
- The commands that are given in square brackets are optional.
- The commands given in curly brackets indicate that one of the two options can be used, but not both.
- INCREMENT BY specifies the interval between two sequential values.
- START WITH clause establishes the starting value for the sequence.
- MAXVALUE and MINVALUE clauses establish a minimum or maximum value for the sequence...
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
CREATE TABLE MOVIE(
mov_id int NOT NULL,
mov_title varchar(255) NOT NULL,
mov_releaseDate date,
gen_id int NOT NULL,
PRIMARY KEY (mov_id),
FOREIGN KEY (gen_id) REFERENCES GENRE(gen_id)
);
CREATE TABLE GENRE(
gen_id int NOT NULL,
PRIMARY KEY (gen_id),
gen_name varchar(255) NOT NULL
);
Search movies by year. Using the tables above, create a stored procedure, called getMovieByYear, that, given a year, returns all the movies which were released in that year. It displays the movie title, movie’s genre name, and release year.please use MySQL Workbench with screenshots of it for the explanation.
CREATE TABLE MOVIE(
mov_id int NOT NULL,
mov_title varchar(255) NOT NULL,
mov_releaseDate date,
gen_id int NOT NULL,
PRIMARY KEY (mov_id),
FOREIGN KEY (gen_id) REFERENCES GENRE(gen_id)
);
CREATE TABLE GENRE(
gen_id int NOT NULL,
PRIMARY KEY (gen_id),
gen_name varchar(255) NOT NULL
);Search movies by year. Using the tables above, create a stored procedure, called getMovieByYear, that, given a year, returns all the movies which were released in that year. It displays the movie title, movie’s genre name, and release year.
Write a SELECT statement that returns all rows with salary, last name, street address and ZIP code (SALARY, LAST_NAME, STREET, ZIP_CODE) from the EMPLOYEE and ADDRESS tables, sorted by SALARY from largest to smallest. STREET and ZIP_CODE are in the ADDRESS table, while the other fields are in the EMPLOYEE table. The common key is EMPLOYEE_ID.
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
What happens to the existing rows of a table if the DEFAULT value of a column is changed?
arrow_forward
Delete the index named ITEM_INDEX3.
arrow_forward
There's a "friend_requests" table that has a column "action_taken" that displays if the friend request was rejected, requested, or accepted.Implement a SQL query to return the number of "action_taken" that were "accepted".
arrow_forward
Remember this while removing rows, columns, and tables:
arrow_forward
There's a "friend_requests" table that has a column "action_taken" that displays if the friend request was rejected, requested, or accepted.Implement a SQL query to return the percentage of "action_taken" that were "accepted".
arrow_forward
You have a query that filters a BigQuery table using a WHERE clause on timestamp and ID columns. By using bq query -- -dry_run you learn that the query triggers a full scan of the table, even though the filter on timestamp and ID select a tiny fraction of the overall data
a. Create a separate table for each ID.
B Use the LIMIT keyword to reduce the number of rows returned.
C Recreate the table with a partitioning column and clustering column.
D Use the bq query - -maximum_bytes_billed flag to restrict the number of bytes billed.
arrow_forward
Using the WideWorldDistributors database and the Sales schema create a function that takes InvoiceID as an input parameter and returns all the rows from the table InvoiceLines for the InvoiceID value passed into the function. If the result is a NULL return a zero
arrow_forward
Indexing can clearly be very beneficial. Why should younot create an index for every column of every table of yourdatabase?
arrow_forward
9. Write a trigger that prohibits duplicate values except for nulls in the NoDupName column of the following table: CREATE TABLE TestUniqueNulls (RowID NoDupName varchar(20) int IDENTITY NOT NULL, NULL); (Note that you can’t do this by using a unique constraint because the constraint wouldn’t allow duplicate null values.) If an INSERT or UPDATE statement creates a duplicate value, roll back the statement and return an error message. Write a series of INSERT statements that tests that duplicate null values are allowed but duplicates of other values are not.
arrow_forward
PLZ help with the following question
USE ORACLE SQL or SQL LIVE
Create an Oracle sequence to generate values for volunteer numbers. The sequence should startwith 9000 and increment by 10. Do not cache any values.
The databse:
CREATE TABLE Packinglist( List_ID INT NOT NULL, Name VARCHAR(50), Description VARCHAR(100), PRIMARY KEY(List_ID));
CREATE TABLE Task( Task_Code INT NOT NULL, List_ID INT, Task_Description VARCHAR(100), Type VARCHAR(30), Status VARCHAR(20), PRIMARY KEY(Task_Code), FOREIGN KEY(List_ID) REFERENCES Packinglist(List_ID));
CREATE TABLE Volunteer( Vol_ID INT NOT NULL, Name VARCHAR(50), Telephone NUMBER, Address VARCHAR(100), PRIMARY KEY(Vol_ID));
CREATE TABLE Assignment( Vol_ID INT, Task_Code INT, Start_Time TIME, End_Time TIME, PRIMARY KEY(Vol_ID, Task_Code), FOREIGN KEY(Vol_ID) REFERENCES Volunteer(Vol_ID), FOREIGN KEY(Task_Code) REFERENCES Task(Task_Code));
CREATE TABLE Package( Pack_ID INT NOT NULL, Task_Code…
arrow_forward
Which of the following statements are true about heap organized tables?
QuestionInstructionsCode
Choices - Select all that apply.
Expand AllCollapse All
Choice 1
They are the default table type in Oracle Database
Choice 2
They enforce an order on how the data is physically stored
Choice 3
They can group similar rows from different tables together
Choice 4
You can partition a heap table
arrow_forward
Write a script that adds an index to the MyGuitarShop database for the zip code field in the Addresses table.
Write a script that implements the following design in a database named MyWebDB:
In the Downloads table, the UserID and ProductID columns are the foreign keys.
In the Users table, the EmailAddress and LastName columns are required, but the FirstName column is not.
Include a statement to drop the database if it already exists. Include statements to create and select the database. Include any indexes that you think are necessary.
3. Write a script that adds rows to the database that you created in exercise 2. Add two rows to the Users and Products tables. Add three rows to the Downloads table: one row for user 1 and product 1; one for user 2 and product 1; and one for user 2 and product 2. Use the GETDATE function to insert the current date and time into the DownloadDate column.
Write a SELECT statement that joins the three tables and retrieves the data from these tables like…
arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr