Related questions
Topic:
Software: SQLite
Schema Table:
CREATE TABLE name_basics (nconst VARCHAR(45) NOT NULL, primaryName VARCHAR(45) NULL, birthYear YEAR(4) NULL, deathYear YEAR(4) NULL, age INT NULL, movieNum INT NULL, PRIMARY KEY (nconst));
CREATE TABLE title_episode (episodeID VARCHAR(45) NOT NULL,
parentTconst VARCHAR(45) NULL, seasonNumber INT NULL,
episodeNumber INT NULL, PRIMARY KEY (episodeID), CONSTRAINT parentTconst FOREIGN KEY (parentTconst) REFERENCES title_basics (tconst) ON DELETE NO ACTION ON UPDATE NO ACTION);
CREATE TABLE title_principals (tconst VARCHAR(45) NOT NULL, ordering INT NULL, nconst VARCHAR(45) NULL, category VARCHAR(45) NULL,
job VARCHAR(45) NULL, characters VARCHAR(45) NOT NULL, PRIMARY KEY (tconst, characters), CONSTRAINT nconst FOREIGN KEY (nconst)
REFERENCES name_basics (nconst) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT tconst FOREIGN KEY (tconst) REFERENCES title_basics (tconst) ON DELETE NO ACTION ON UPDATE NO ACTION);
CREATE TABLE title_ratings (tconst VARCHAR(45) NOT NULL, averageRating VARCHAR(45) NOT NULL, numVotes VARCHAR(45) NULL,
PRIMARY KEY (tconst, averageRating), CONSTRAINT tconst FOREIGN KEY (tconst) REFERENCES title_basics (tconst) ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE TABLE types (typeID INT NOT NULL, types VARCHAR(45) NULL, PRIMARY KEY (typeID));
CREATE TABLE types_collection (akasID INT NOT NULL, typeID INT NOT NULL, PRIMARY KEY (akasID, typeID), CONSTRAINT akasID FOREIGN KEY (akasID) REFERENCES title_akas (akasID) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT typeID FOREIGN KEY (typeID) REFERENCES type (typeID) ON DELETE NO ACTION ON UPDATE NO ACTION);
Prompt: Add triggers to the above schema table that automatically updated when new data in inserted.
Step by stepSolved in 2 steps
- Using the Henry Books database, add the new title from Stephen King to the wrote table: Book code = 3578 Author number = 6 Edition = 1 You will insert 2 snips for this question. What construct did you use to add a row to the wrote table. Insert the snip of the construct used: Insert the snip of the wrote table. Be sure the new row is displayed:arrow_forwardorder db orderid int custid int itemcode int orderquantity int deliverydate date modeofpayment char(1) date_of_order datearrow_forwardSQL Question~ Write SQL expression to list flight_no and airline_code of all the bookings for customers with first name Sam and city Chicagoarrow_forward
- What are examples of aggregate functions in SQL?arrow_forwardSQL Database Designs What do you think is the most important concept in SQL Database Designsarrow_forwarddatabase: 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_forward
- use 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_forwardHow are clustered and non-clustered indexes different in SQL?arrow_forwardUse your ERD to define the table structures in the database using SQL DDL commands to build your schema.arrow_forward
- Write 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_forwardINFO 2103 Database Programming Assignment #3 For this assignment, please use the SUPPLIER and COMPUTER table. The script is available in Google Classroom. Script name: computer.sql. Note: Use Subqueries Display the name of supplier(s) that supplied the most expensive computer? 2. Display the name of supplier(s) that supplied more expensive computer than that being supplied by IBM. 3. Display the supplier that supplied the most computers. 4. Display the building location that has the most computersarrow_forwardI need help with this question for my Database Management class for SQL developer. Thank you REP (REP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, POSTAL_CODE, COMISSION, RATE) CUSTOMER (CUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, POSTAL_CODE, BALANCE, CREDIT_LIMIT, REP_NUM) ORDERS (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM) ORDER_LINE (ORDER_NUM, ITEM_NUM, NUM_ORDERED, QUOTED_PRICE) ITEM (ITEM_NUM, DESCRIPTION, ON_HAND, CATEGORY, STOREHOUSE, PRICE) Question: List TOY category items and orders placed for them. Report: Item_Num, Description, Order_Num, and Order_Datearrow_forward
- Text book imageComputer Networking: A Top-Down Approach (7th Edi...Computer EngineeringISBN:9780133594140Author:James Kurose, Keith RossPublisher:PEARSONText book imageComputer Organization and Design MIPS Edition, Fi...Computer EngineeringISBN:9780124077263Author:David A. Patterson, John L. HennessyPublisher:Elsevier ScienceText book imageNetwork+ Guide to Networks (MindTap Course List)Computer EngineeringISBN:9781337569330Author:Jill West, Tamara Dean, Jean AndrewsPublisher:Cengage Learning
- Text book imageConcepts of Database ManagementComputer EngineeringISBN:9781337093422Author:Joy L. Starks, Philip J. Pratt, Mary Z. LastPublisher:Cengage LearningText book imagePrelude to ProgrammingComputer EngineeringISBN:9780133750423Author:VENIT, StewartPublisher:Pearson EducationText book imageSc Business Data Communications and Networking, T...Computer EngineeringISBN:9781119368830Author:FITZGERALDPublisher:WILEY