SQL PROGRAMMING QUESTION: 1. Using the \'books\' database (which can be found in
ID: 3741222 • Letter: S
Question
SQL PROGRAMMING QUESTION:
1. Using the 'books' database (which can be found in the attached)
- Write a one to many join that will select all of the author's first and last names where their royalty share is 1.00. (authors, authors_title)
- Write the same using, but using the subquery method
2. Write the create statement for this table:
Table name: Customer
- Primary Key
- Firstname, lastname, address, email, age, gender fields
Table name: Bank Account
- Primary key
- Account "nickname" text field
- Date field with a default date value of the date the record was created
- Foreign key linked to the customer table
- "Date of last transaction" field
- Account balance
3. Define the proper constraints: nulls, uniques, primary and foreign keys.
Explanation / Answer
1. SELECT fname,lname FROM authors
WHERE author_id = (SELECT author_id FROM royalty WHERE share = 1.00);
2. CREATE table Customer(
ID int NOT NULL UNIQUE,fname varchar(255), lname varchar(255),address varchar(255),
email varchar(255), age int, gender varchar(255),
PRIMARY KEY(ID)
);
CREATE table Account(
ID int NOT NULL UNIQUE,nickname varchar(255), start_date,
start_date date DEFAULT GETDATE(),
PRIMARY KEY(ID), FOREIGN KEY (ID) REFERENCES Customer(ID)
);
**Comment for any further queries.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.