Related questions
Concept explainers
PLZ HELP WITH THE FOLLOWING:
1. Fix the sql file so that the values can properly be inserted.
CREATE TABLE MEMBERSHIP
( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEM_NUM_PK PRIMARY KEY,
MEM_FNAME VARCHAR(30) NOT NULL,
MEM_LNAME VARCHAR(30) NOT NULL,
MEM_STREET VARCHAR(15),
MEM_CITY VARCHAR(10),
MEM_STATE CHAR(2),
MEM_ZIP CHAR(5),
MEM_BALANCE NUMBER (2));
CREATE TABLE RENTAL
( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY,
RENT_DATE DATE,
MEM_NUM CHAR(3),
CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP);
CREATE TABLE PRICE
(PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICE_CODE_PK PRIMARY KEY,
PRICE_DESC VARCHAR(20),
PRICE_RENTFEE NUMBER (3,1),
PRICE_DAILYATFEE NUMBER(3,1));
CREATE TABLE MOVIE
(MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIE_NUM_PK PRIMARY KEY,
MOVIE_NAME VARCHAR(30) NOT NULL,
MOVIE_YEAR CHAR(4),
MOVIE_COST NUMBER(5,2),
MOVIE_GENRE VARCHAR(15),
PRICE_CODE CHAR(1),
CONSTRAINT MOVIE_PRICE_CODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES PRICE);
CREATE TABLE VIDEO
(VID_NUM CHAR(5) CONSTRAINT VIDEO_VIDEO_NUM_PK PRIMARY KEY,
VID_INDATE DATE,
MOVIE_NUM CHAR(4),
CONSTRAINT VIDEO_MOVIE_NUM_FK FOREIGN KEY (MOVIE_NUM) REFERENCES MOVIE);
CREATE TABLE DETAILRENTAL
(RENT_NUM CHAR(4),
VID_NUM CHAR(5),
DETAIL_FEE NUMBER(3,1),
DETAIL_DUEDATE DATE,
DETAIL_RETURNDATE DATE,
DETAIL_DALYLATEFEE NUMBER(2),
CONSTRAINT DETAILRENTAL_RENT_VID_PK PRIMARY KEY(RENT_NUM, VID_NUM),
CONSTRAINT DETAILRENTAL_RENT_FK FOREIGN KEY (RENT_NUM) REFERENCES RENTAL,
CONSTRAINT DETAILRENTAL_VID_FK FOREIGN KEY (VID_NUM) REFERENCES VIDEO);
-- Values which need to be inserted.
INSERT INTO MEMBERSHIP VALUES('102', 'Tami', 'Dawson', '2632 Takli Circle', 'Norene', 'TN', '37136', 11);
INSERT INTO MEMBERSHIP VALUES('103', 'Curt', 'Knight', '4025 Cornell Court', 'Flatgap', 'KY', '41219', 6);
INSERT INTO MEMBERSHIP VALUES('104', 'Jamal', 'Melendez', '788 East 145th Avenue', 'Quebeck', 'TN', '38579', 0);
INSERT INTO MEMBERSHIP VALUES('105', 'Iva', 'Mcclain', '6045 Musket Ball Circle', 'Summit', 'KY', '42783', 15);
INSERT INTO MEMBERSHIP VALUES('106', 'Miranda', 'Parks', '4469 Maxwell Place', 'Germantown', 'TN', '38402', 5);
INSERT INTO RENTAL VALUES('1001', TO_DATE('01-MAR-09', 'DD-MM-YY'), '103');
INSERT INTO RENTAL VALUES('1002', TO_DATE('01-MAR-09', 'DD-MM-YY'), '105');
INSERT INTO RENTAL VALUES('1003', TO_DATE('02-MAR-09', 'DD-MM-YY'), '102');
INSERT INTO PRICE VALUES('1', 'Standard', 2.0, 1);
INSERT INTO PRICE VALUES('2', 'New Release', 3.5, 3);
INSERT INTO PRICE VALUES('3', 'Discount', 1.5, 1);
INSERT INTO PRICE VALUES('4', 'Weekly Special', 1, 0.5);
INSERT INTO MOVIE VALUES('1234', 'The Cesar Family Christmas', 2007, 39.95, 'FAMILY', '2');
INSERT INTO MOVIE VALUES('1235', 'Smokey Mountain Wildlife', 2004, 59.95, 'ACTION', '3');
INSERT INTO MOVIE VALUES('1236', 'Richard Goodhope', 2008, 59.95, 'DRAMA', '2');
INSERT INTO MOVIE VALUES('1237', 'Beatnik Fever', 2007, 29.95, 'COMEDY', '2');
INSERT INTO MOVIE VALUES('1238', 'Constant Companion', 2008, 89.95, 'DRAMA', '2');
INSERT INTO MOVIE VALUES('1239', 'Where Hope Dies', 1998, 25.49, 'DRAMA', '1');
INSERT INTO MOVIE VALUES('1245', 'Time to Burn', 2005, 45.49, 'ACTION', '3');
INSERT INTO MOVIE VALUES('1246', 'What He Doesnt Know', 2006, 58.29, 'COMEDY', '1');
INSERT INTO VIDEO VALUES('54321', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');
INSERT INTO VIDEO VALUES('54324', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');
INSERT INTO VIDEO VALUES('54325', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');
INSERT INTO VIDEO VALUES('34341', TO_DATE('22-JAN-07', 'DD-MM-YY'), '1235');
INSERT INTO VIDEO VALUES('34342', TO_DATE('22-JAN-07', 'DD-MM-YY'), '1235');
INSERT INTO VIDEO VALUES('34366', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');
INSERT INTO VIDEO VALUES('34367', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');
INSERT INTO VIDEO VALUES('34368', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');
INSERT INTO VIDEO VALUES('34369', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');
INSERT INTO VIDEO VALUES('44392', TO_DATE('21-OCT-08', 'DD-MM-YY'), '1237');
INSERT INTO DETAILRENTAL VALUES('1001', '34342', 2, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('02-MAR-09', 'DD-MM-YY'), 1);
INSERT INTO DETAILRENTAL VALUES('1001', '61353', 2, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('03-MAR-09', 'DD-MM-YY'), 1);
INSERT INTO DETAILRENTAL VALUES('1002', '59237', 3.5, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('04-MAR-09', 'DD-MM-YY'), 3);
INSERT INTO DETAILRENTAL VALUES('1003', '54325', 3.5, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('09-MAR-09', 'DD-MM-YY'), 3);
INSERT INTO DETAILRENTAL VALUES('1003', '61369', 2, TO_DATE('06-MAR-09', 'DD-MM-YY'), TO_DATE('09-MAR-09', 'DD-MM-YY'), 1);
INSERT INTO DETAILRENTAL VALUES('1003', '61388', 0, TO_DATE('06-MAR-09', 'DD-MM-YY'), TO_DATE('09-MAR-09', 'DD-MM-YY'), 1);
INSERT INTO DETAILRENTAL VALUES('1004', '44392', 3.5, TO_DATE('05-MAR-09', 'DD-MM-YY'), TO_DATE('07-MAR-09', 'DD-MM-YY'), 3);
INSERT INTO DETAILRENTAL VALUES('1004', '34367', 3.5, TO_DATE('05-MAR-09', 'DD-MM-YY'), TO_DATE('07-MAR-09', 'DD-MM-YY'), 3);
Step by stepSolved in 2 steps
- 2. Write a SQL statement to insert a new user with userId ‘XXXXX’ into the users table and populate at least 5 other columns including column userName. Note: userName column is varchar(20) and its value must be unique in the table users. DO NOT insert into the “created” column but pay attention to it’s value after the insert Paste the SQL below.arrow_forwardWrite an SQL query that will select OrderID and OrderDate from theOrders table and OrderDetailID from the OrderDetails table. The queryshould join on the Orders table using the OrderID attribute.arrow_forwardCreate a table in SQL developer and name it as ‘MyCrush’. The table should consists of only three attributes as follows: Crush_ID which will be the primary key with the number length of .Crush_Name with the maximum character length of 30.Crush_Description with the maximum character length of 10.Insert exactly three records with the above given attributes. Crush_ID values should be 1, 2 and 3 for the three records. Crush_Description should be ‘Present’ for record 1, ‘Past’ for record 2 and ‘Childhood’ for record 3. If you don’t have any crush from your childhood to present, you can insert some random names. Hmm.. Seems Interesting!! It’s time to implement your programming skills using PL/SQL stored procedures and functions. Create another table called ‘MyDetails’ and should contain exactly four attributes as below: Student_ID,Student_Name ,Student_Term ,Student_GPA If you are not a Master’s student, let’s assume you are a student pursuing masters and taking four semesters or terms.…arrow_forward
- CREATE TABLE Employee( firstName char(30) , lastName char(30) , hireDate date ,empNo int , empInitial char(30) , years int ); Write the SQL syntax to create an insert statement for the employee table you created above.arrow_forwardPlease help with pl/sql block!arrow_forwardWrite Create Table SQL statements based on the above ERD. All tables must have primary keys. All tables must have appropriate foreign key constraints. Foreign keys must have the same name, datatype and size of the primary key that they refer to The following columns datatypes and sizes must be used: bid,authid / number(4) title,sname,fname / varchar2(30) sellingprice, payrate / number(6,2) Table Column Type Range author sname & fname Unique book title Not Null book sellingprice Check not negative allocation payrate Check 1 to 79.99arrow_forward
- database: sql Q1. Create the following tables: Carrier Attribute name Type Size Constraint carrierID Varchar2 10 Primary key carrierName Varchar2 100 NOT NULL Phone Varchar2 50 LIKE ‘+966%’ Range Varchar2 30 Value is either ‘International’ or ‘Local’ Review Attribute name Type Size Constraint ReviewID INT Primary key Stars INT NOT NULL Comment Varchar2 70 Name Varchar2 50 Default ’User’ CustomerID INT Foreign key references customers ProductID INTarrow_forwardPLZ help with the following using ORACLE command sql Use subqueries CREATE TABLE MEMBERSHIP ( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEM_NUM_PK PRIMARY KEY, MEM_FNAME VARCHAR(30) NOT NULL, MEM_LNAME VARCHAR(30) NOT NULL, MEM_STREET VARCHAR(15), MEM_CITY VARCHAR(10), MEM_STATE CHAR(2), MEM_ZIP CHAR(5), MEM_BALANCE NUMBER (2)); CREATE TABLE RENTAL ( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY, RENT_DATE DATE, MEM_NUM CHAR(3), CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP); CREATE TABLE PRICE (PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICE_CODE_PK PRIMARY KEY, PRICE_DESC VARCHAR(20), PRICE_RENTFEE NUMBER (3,1), PRICE_DAILYATFEE NUMBER(3,1)); CREATE TABLE MOVIE (MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIE_NUM_PK PRIMARY KEY, MOVIE_NAME VARCHAR(30) NOT NULL, MOVIE_YEAR CHAR(4), MOVIE_COST NUMBER(5,2), MOVIE_GENRE VARCHAR(15), PRICE_CODE CHAR(1), CONSTRAINT MOVIE_PRICE_CODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES PRICE); CREATE TABLE VIDEO…arrow_forwarduse oracle sql developper or Oracle sql developper problem in picture the database CREATE TABLE MEMBERSHIP( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEM_NUM_PK PRIMARY KEY,MEM_FNAME VARCHAR(30) NOT NULL,MEM_LNAME VARCHAR(30) NOT NULL,MEM_STREET VARCHAR(15),MEM_CITY VARCHAR(10),MEM_STATE CHAR(2),MEM_ZIP CHAR(5),MEM_BALANCE NUMBER (2)); ALTER TABLE MEMBERSHIPMODIFY MEM_STREET VARCHAR(25);CREATE TABLE RENTAL( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY,RENT_DATE DATE,MEM_NUM CHAR(3),CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP); CREATE TABLE PRICE(PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICE_CODE_PK PRIMARY KEY,PRICE_DESC VARCHAR(20),PRICE_RENTFEE NUMBER (3,1),PRICE_DAILYATFEE NUMBER(3,1)); CREATE TABLE MOVIE(MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIE_NUM_PK PRIMARY KEY,MOVIE_NAME VARCHAR(30) NOT NULL,MOVIE_YEAR CHAR(4),MOVIE_COST NUMBER(5,2),MOVIE_GENRE VARCHAR(15),PRICE_CODE CHAR(1),CONSTRAINT MOVIE_PRICE_CODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES…arrow_forward
- SQL Code for Extend the code in (Perform a LEFT OUTER JOIN between the tables Wicketkeeping and Batting. You may select any column(s). Look at the result and state how many wicketkeepers have not played as batsmen (no need to write code for this part ) To show the name of the fielders who have played 7 times as many matches as the wicketkeeper(s) in 5).arrow_forwardWrite SQL statements to create at the two tables for the database. The tables must have at least three relevant columns with appropriate types, a primary key and at least one table should have a foreign key and the related foreign key constraints. CREATE TABLE students ( Student_id int NOT NULL PRIMARY KEY, Stud_firstname VARCHAR(30) NOT NULL, Stud_lastname VARCHAR(30) NOT NULL, Stud_email VARCHAR(80) NOT NULL UNIQUE advisor_id int, ); create table advisor ( advisor_id int not null primary key, stud_id int, adv_firstname varchar(30), adv_lastname varchar(30), foreign key(student_id) references students(student_id) ); [keep getting error, need help correcting]arrow_forwardhelp with sql class Report the names of the professors who work on the most number of projectsarrow_forward
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill Education
Starting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSON
Digital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON 
C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSON
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Programmable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education