Databases Questions and Answers August 13, 2021

TABLE Customers-- ------------------- -- Add a Not Null constraint on LastName and FirstName by altering table and modifying the column -- Add a primary key constraint on Customers table using Customer# column -- Add a check constraint on region column to verify if it is with in the following region ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E') --

Create the following table using a SQL Script; 1. Table Name Students_{YourStudentID} e.g Students_C0697697 Student_ID Varchar(10) FirstName Varchar(50) LastName Varchar(100) Telephone_Number Varchar(10) Age int City Varchar(50) 2. Add a Primary Key Constraint to Student_ID 3. Add a NOT NULL Constraint to LastName 4. Add a UNIQUE Constraint to

1 Create the MYDEPT table based on the following table instance chart. Confirm that the table is created. Column Name Key Type NullsUnique FK Table FK Column Data type Length Column Name ID Key Type NullsUnique FK Table FK Column Data type Length ID Number 7 7 Number 2. Create the MYEMP table based on the following table instance chart. Confirm

DEFINE TABLE AUTHORS WITH COLUMNS AUTHORID INT AND AUTHORNAME VARCHAR(30),ADD COLUMN LEVEL PRIMARY KEY CONSTRAINT FOR AUTHORID, ADD COLUMN LEVEL NOT NULL CONSTRAINT FOR AUTHORNAME 2. DEFINE TABLE PUBLISHERS WITH COLUMNS PUBLISHERID INT AND PUBLISHERNAME VARCHAR(30), ADD TABLE LEVEL PRIMARY KEY CONSTRAINT FOR PUBLISHERID, ADD TABLE LEVEL UNIQUE

write out the DDL statement, but also add a column level primary key constraint to the Id field. CREATE TABLE emp (id NUMBER(7), last_name VARCHAR2(25), first_name VARCHAR2(25), dept_id NUMBER(7) )

Use the alter table statement to add a primary key constraint for the rider_student table. 6. Use the alter table statement to add a primary key constraint for the rider_major table. 7. Use the create table statement to create a table using the create table-like syntax. Create a customer table in your database using the dbclass.customer table. 8.

Create another table PART2. The fields and correspondent data types are Field Data Type PID (primary key) CHAR(3), Not null Pname Color Weight Pcity Quantity DesignDate VARCHAR(40) VARCHAR(20) NUMERIC(5,2) VARCHAR(20) INTEGER DATE ase name your own primary key constraint

fix my SQL code Create table Customer ( CustID INT NOT NULL CustFName CustLName CustStreet CustCity CustState CustZip CustPhone CustEmail IsMember MemExpiration CONSTRAINT CustID PRIMARY KEY CONSTRAINT FOREIGN KEY );

AutoSave OFF BES Home Insert Draw Page Layout X Paste N15 E 1 custCity 2 Dayton OH 3 Dayton OH 4 Cincinnati OH 5 Dayton OH 6 Cincinnati OH 7 Cincinnati OH 8 Cincinnati OH 9 Dayton OH OH 10 Dayton II 11 Chicago 12 Chicago 13 Chicago 14 Chicago 15 Chicago 16 Chicago 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

Given the following CREATE TABLE statement, add primary key constraint that sid is primary key and another constraint that rating should be between 0 and 10. CREATE TABLE Sailors (sid INTEGER, sname CHAR(10), rating INTEGER, age REAL)

The given SQL creates an Album and Song tables. The SHOW COLUMNS queries show information about the Album and Song table columns. Modify the two CREATE TABLE statements. Add a primary key constraint to the Album table's ID column. Add a primary key constraint to the Song table's ID column, and add a foreign key constraint so the AlbumID column

(Using Oracle SQL) How do I find the primary key of this table? And then how do I consequently drop the primary key from the table? Edit: I was already give this code and it did not work select constraint_name,constraint_type from user_constraint where table_name='CUSTOMER';

Page 2 of 4 665 words select from customer 1 2 3 4 5 6 7 8 9 Script Output x Query Result x SQL Use ONE SQL statement to find out the name of the primary key constraint defined on Customer table. (Your SQL should return something like SYSC0021715, numbers on your account may differ.) Then use another SQL statement to drop the primary key

Table Football Name Touchdowns John Thomas 55 Dave Roberts 43 Charles Lowe 22 Brian Jones 30 Table Basketball Name Daniel Wayne Ron Stanley Peter Giles 11 Brandon Miles 6 12 32 Field Goals 35 40 25 31 Points Scored Interceptions Years Played Fumbles 12 5 14 18 3 15 7 10 8 4 7 5 Years Played Division Eastern Eastern Western Southern Division

In the timestamp ordering (aka timestampbased) concurrency control algorithm, explain why a new timestamp should be assigned to a transaction when it is rolled back and then resubmitted. You dont need to describe the timestamp ordering concurrency control algorithm, and dont need to show an example schedule. 4. (15 points) Convert the EER schema

Using C language and PIC16F917 microcontroller, implement PID control for fan motor speed control and project design

Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) 1. Write the primary key for the below tables: Primary Key Constraint for Hotel Table is: Primary Key Constraint for Room Table is: Primary Key Constraint for Booking Table is: Primary

(Using Oracle SQL) How do I find the primary key of this table? And then how do I consequently drop the primary key from the table?

Page 2 of 4 1 2 3 4 5 6 7 8 665 words Script Output x Query Result x 9 select from customer Use ONE SQL statement to find out the name of the primary key constraint defined on Customer table. (Your SQL should return something like SYSC0021715, numbers on your account may differ.) Then use another SQL statement to drop the primary key constraint

How can we define in XMLSchema a) primary key constraint b) child only element c) empty element? (explain the steps, command example)

In the following SQL statements, some tables are created with different constraints. Give one primary key constraint and one foreign key constraint and specify in which table each is given CREATE TABLE CustomerT (CustomerID CustomerName CustomerAddress Customer City Customer State CREATE TABLE Order T (OrderID Order Date CustomerID

fix my SQL code Create table Order ( OrderNum INT NOT NULL CustID ShippingStreet ShippingCity ShippingState ShippingZip OrderDate DeliveryChange PaymentInformation CreditConfirmation CONSTRAINT OrderNum PRIMARY KEY CONSTRAINT FOREIGN KEY

Home Insert Draw Page Layout 1 2 J24 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 AutoSave OFF BES 5 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 V Paste X P Ready A X A B orderNum custID 1001 C10 1002 C50 1003 C20 1004 C30 1005 CAO 1006 C100 1007 C90 1008 C70 1009 C140 1010 C90 1011 C80 1012 C60 1013 C20 1014 C90

Write the SQL statement needed to create this table. Must include proper data types and primary key constraint as specified in the ERD. Do not worry about the creation of the schema.

person id INT NN firstnam e VARCHAR(45) NN lastname VARCHAR (45) NN date of birth DATE NN Indexes PRIMARY

For student 2 only please

Create the following queries using Data Manipulation Language (DML)student must be able to explain the queries. Student 1 1. List the name, position, and salary of employees from branch BRO12, and arrange the list alphabetically according to the names. List the details of employees who work at any branch that is based in Penang state. List the

HOW TO CREATE A DATABASE AND ITS TABLES with SQL STATEMENTS b. AND - And this is IMPORTANT - Write it in MYSQL (NOT ORACLE etc) I AM USING Murach's SQL Server 2019 For developers . Chap 11 has much of the information if you need reference. This problem here involves using Data Definition Language (DDL) and Data Manipulation Language (DML) PROBLEM

PK Animal Name Gender Lives In Zoo PK ZID Name Location Type Of H Animal Type Type Enclosure Regular Employee PK RID Works In FK CID FK CID FK ZID FK BID Paid Employee PK SSN EID EFName ELName Contact State Enclosure Assigned, 0 Email Address DID Works Under Contractor PK CID FK BID Work In Assigned Assigned Belongs To Assigned Work In

Need Parser code only

Lexical Analyzer For the first task of the frontend, you will use flex to create a scanner for the Structured Query Language (SQLMySQL). Figure out all the tokens your query language allows and write them out in a Lex file. Submit the Lex file alongwith example source files. Make sure examples cover most of the queries supported by SQL. At least

For the Statement of Requirements (SOR) scenario below, create the following 1. ERD (Entity Relationship Diagram) 2. RDM (Relational Data Model) 3. SQL Data Definition Language (DDL) statements to define this database structure 4. SQL Data Manipulation Language (DML) statements to populate data into this database 5. SQL Data Manipulation Language

Write a code for MySQL for the following query "List all recipes that are main courses (recipe class is 1) and that have notes. (4 rows)."

Administration SCHEMAS Q Filter objects A EntertainmentAgencyE... A A on BowlingLeagueExample databasename Schemas newschema Recipes Example Tables IngredientClasses Ingredients Measurements RecipeClasses Columns Indexes Foreign Keys Triggers RecipeIngredients Recipes RecipeClassID RecipeClassDe... SalesOrdersExample Views Stored Procedures

Provide examples of both clientside and serverside scripts on your website.,Your website should be hard coded using only a text editor. Use visual studio code to do this assignment Make a more comprehensive, multilevel personal website. It will contain at least four components in its structure (each component is a page in a multipage website.) a

How to delete EMPLOYEE_ID | Problems stems from sql only recognizing rows instead of columns.

Error starting at line 1 in command delete from importemployeecc where columnid8 Error at Command Line 2 Column 7 Error report SQL Error ORA00904 COLUMNID invalid identifier

Some songs were not released as part of an album and thus are noted with NULL for their albumid. INSERT a new album called Single and UPDATE songs with a NULL albumid to belong to this new album. (Include screenshots of the relevant queries used and their output)

Create the Relation using MySQL (Create that table A) that matches this ER diagram. Normalization does not need to be considered. Put the fieldsattributes in alphabetical order. You can assume all the attributesfields are INT unsigned NOT NULL After you create the table, then issue the command DESCRIBE A G E B 00 D A LL F UI

List the lunch ID, lunch date, the count of menu items per purchased lunch, and the total price of a purchased lunch per lunch id. Only include lunch dates from the second half of May 2021 (May 16, 2021 May 31, 2021). Label the total price expression as TOTALPRICE and the count of items as COUNT. Format the TOTALPRICE in currency format. Sort by

what type of data processing and tools would be required to create that visualization. A picture of or link to your chosen visualization ( I need the link ) Now that learned a bit about relational databases, let's take a look at data visualization again -- visualizations are fun and powerful and are worth a second look :). As before, find a

Following ones I have listed represent some that have obvious implications: Variety Vocabulary Virality Venue Volatility Verbosity Versatility Validity Choose four of these and describe how they impact what we have learned in the course up to this point. Your discussion should focus on some combination of database transactions and their propreties

Create a new table - ASSIGNMENT1 . Includes steps to create this table. The table has one column - COLUMNA and 100 rows. Create a script to insert rows into ASSIGNMENT1 table. There needs to be several rows which have a text string beginning with the letter 'A', several rows beginning with the letter 'M' and several rows beginning with the letter

COST Translate the following ER Diagram into a relational database schema. REGISTRATION VIN FITTED VEHICLE LEGAL OPTION TAX DISC DURATION EXPIRY COST DEPENDS CLASS TYPE NOTES

write down any assumptions that you made. An assumption may not be in contradiction with the given text.,This firm assists two types of customers the business people and the public. They are three partners who specialize in different types of legal cases. Their office is in Dubai and has branches in all emirates of the UAE. Each branch is

a) show the result of the SQL query. Write column headers in gray cells. DECLARE maxRating FLOAT (SELECT MIN(Rating) FROM Movies) SELECT Title, Year, Rating FROM Movies WHERE Rating maxRating,SELECT Title, Year, Rating, RANK() OVER (ORDER BY Rating DESC) AS Rank FROM Movies WHERE Year 1999 ORDER BY Rank DESC,Movies Title Batman Robin SpiderMan

Show each agents name, the sum of the contract price for the engagements booked, and the agents total commission for agents whose total commission is more than $700

Entertainment Agency Example Database CUSTOMERS .......... HH CustomerlD CustFirstName CustLastName CustStreetAddress PK HH CustCity CustState CustZipCode CustPhoneNumber MUSICAL PREFERENCES ... CustomerID StylelD PreferenceSeq StylelD StyleName ......... CPK CPK MUSICALSTYLES ......... ...... ....... PK HE ENGAGEMENTS EngagementNumber PK

How is metal, paint and soil evidence collected Where is it typically found What testing methods are used to test metals, paint and soil Refer to the portion of the text which deals with the evidence from the President Kennedy Assassination. Explain how the analysis of trace elements was important to the investigation of the assassination of

Using your music database assume there is a user with userid dba625a. how to Give this user access to the music database. - Give this user read access to the entire database - Give this user write access only to the bandrecognition table - Give this user the ability create views - This user should not have the ability to create any other objects

Which column is the primary key in the following Patients table Patients Patient Name Karl Mark Peter Peter Date of Birth 19032000 20051999 10032001 19032000 Email karl.kluckyshrub.com mark.fluckyshrub.com peter.gluckyshrub.com peter.sluckyshrub.com

Write the SQL keyword to create the Players virtual table in MySQL. CREATE For the toolk PlayersVirtualTable AS SELECT FullName, Age FROM Players

Write SQL query for the following - the number of volcanos per country - the number of volcanos of each type - VEI summary statistics - Elevation summary statistics - the most active volcanos (repeated eruptions)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 RECNO YEAR MONTH DAY NAME 98 1598 (null) (null) Banda Api 99 1600 100 1606 1608 101 102 1609 (null) (null) Tengchong 103 1615 3 (null) Banda Api 104 1617 (null) (nul1) Fuego 105 1625 2 Katla 106 1629 (null) (null) Grimsvotn 107 1630 108 1631 109 1631 110 1636 2 1 7 9 9 2 12 5 19 Huaynaputina 23

SQL which will give us the course id number (idCourse field), course description, the number of units, the offering term, whether it's online or in person, and the capacity. Include only those courses which are more than 3 units and have a capacity of less than 40 students. Sort it ascending by idCourse.

Which entries records in the database have NULL values for any of these fields duration, purchase price, production year. The result should be a single row with a single columnvalue.

What's wrong I took off state then another error occurred.

Lab 31 Kim Tay Pet Supplies 0 Tasks 8 CENGAGE MINDTAP LO Create the REP table. Add the provided information to a new record in the REP table. Drop the REP table. Check that the KimTay database only has five tables. Submit 0 query.sql X 1 CREATE TABLE IF NOT EXISTS REP 2 ( 3 repid INT NOT NULL, 4 firstname VARCHAR (50) NOT NULL, 5 lastname

Convert the EERD into a database in MS SQL Server. prepare a New database in the name (Childcare Hospital_group6) Do tables and add attributes according to your EERD Ensure your tables are normalized Identify right data types for each attribute Identify and add SQL Server constraints where necessary (NOT NULL, NULL, Primary Key, Foreign Key,

W File AutoSave Page 5 of 5 Off H Home Insert Draw Design Layout 552 words E Accessibility Investigate chidcare hospital ERD (Word doc.) 21428 ReadOnly Saved to this PC References Mailings. Review View O Search Billing Details Key Key Podent ID Oyweman any Hop 101DE D Py Cashier 10 Patert p DOCTOR DETAL Mo insurance Provider Pa C Hospital address

What would be an SQL Script to add a new table to this Schema for Employee Background with a one to one relationship with the employee table. Add whichever fields are needed to this table, but be sure to include a primary key and if needed a foreign key. You do not need to enter data into this table. Please include one field named with your

regions REGIONID REGION NAME departments DEPARTMENTID DEPART NAME MANAGER ID LOCATION ID jobhistory EMPLO E ID STARTDATE END DATE JOB ID DEPARTMENTID NUMBER VARCHAR2(25) NUMBER VARCHAR2(30) NUMBER NUMBER NUMBER DATE DATE VARCHAR2(10) NUMBER 00 countries COUNTRYID CHAR(2) COUNTRYNAME VARCHAR2(40) REGION ID NUMBER o employees EMPLOYEE ID FIRST NAME

Display the Customer and State from the Addresses table for all addresses located in Florida, followed by those customers located in Georgia using a SET operator. Problem 8 Display the CustomerID and the of Orders for each customer. Use an appropriate alias for the summary column. Problem 9 Display OrderIDs from the Orders table and the orders

Display all columns from the Addresses table for those addresses that are located in New Jersey and have a value in the Line2 column (hint not a null). Order the results by ZinCode in ascending order. Problem 2 Display all columns from the Products table for those products where the ProductName column includes the keyword drum and does not include

Develop a code for MySql to answer the following question: "Select all the DISTINCT countrycodes in the City table and display all the columns." refer to the columns in the table below.

Administration SCHEMAS Q Filter objects Bowling LeagueExample databasename EntertainmentAgencyE... newschema Recipes Example renthw2 world Sales OrdersExample SchoolSchedulingExam... Schemas Tables city Columns ID Name CountryCode District Population Indexes Foreign Keys Triggers country countrylanguage Views Stored Procedures Functions

Draw an E/R diagram for your database and briefly describe the relationships between different tables. (Do not draw the figure by hand, you may use any tools to design or generate your E/R diagram) dataset link: https://www.kaggle.com/datasets/jeegarmaru/us-campaign-finance-20192020-fec table names present in above dataset link: use any website

E Data Explorer 5.88 GB All candidates.csv Any transaction from one com Candidate master.csv Candidatecommittee linkage Committee master.csv Contributions by individuals.c Contributions from committee House Senate current campa Operating expenditures.csv PAC summary.csv

estimate the rate of body fat loss ( body fat lost per day) for bats at Hibernia in each year (2009 and 2016). (Hint think about the slope of the line) Hibernation Time (days) Body Fat (2009) 40 21 80 17.5 120 15 160 12 Body Fat (2016) 27.5 24 19 16 200 10 12.5 About 7.5 body fats were lost at Hibernia in 2009 and 15 body fat lost in 2016

Design a database diagram that allows students to be assigned study-group membership for one or more courses. Each course can have any number of students and each student can be in any number of courses. You can create additional tables and columns, if necessary. Indicate the relationships between tables and identify the primary key and foreign

Write the SQL statement that would be able to i. than 14000 name ii. iii. named tax Define a table constraint that would ensure that every value in the salary field is greater (4marks) Extract the two characters starting from the second character of the drivers (2marks) Computer ta at a rate of 8 for those earning over 30,000 and display output in

Write the same query as 2 but use LEFT JOINS using the PEOPLE table first. This time, sort by salary in descending order and then by first name and year in ascending order. Notes Using the where statement for divide by zero returns 1970 rows, 2,322 rows will be returned using nullif. Order matters in this question. If you JOIN PEOPLE and SALARIES

Using the following Tables Table A Primary Key PKA No foreign Keys. Table B Primary Key PKB Foreign Key PKA (references primary key PKA from Table A) Table C Primary Key PKC Foreign Key PKB (references primary key PKB from Table B) Table D Primary Key PKC and PKE (a composite key) Foreign Key PKC (references primary key PKC from Table C) Foreign

Develop a data model of the proposed employee database utilizing crows foot notation. You are to utilize the entity and attribute names exactly as written, do not make any modifications. (After submission, a selection of three tables will be made by your professor as a sample of your work for grading purposes) EMPLOYEE (EmployeeID, RoleID,

Analyze the different types of databases that can be created and the creation commands both as an online command, as well as using Graphical Interfaces (GUI) and highlight the differences between each one. 2. Include bibliographical references

Preference ordering for (a) the Regime, (b) the moderate Religious Party, and (c) the radical Religious Party over the three possible outcomes. (b) Solve the subgame on the left, where the Religious Party is moderate, as if there were no uncertainty. What is the subgame perfect equilibrium? What is the expected outcome? What are the payoffs that

1. (36 points) In Commitment Problems in Emerging Democracies The Case of Religious Parties, Stathis Kalyvas (2000) examines whether reli gious parties are compatible with secular and liberal democratic institu tions. He concludes that religious parties may be compatible with democ racy as long as they can credibly commit not to impose a

Generate SQL code to make a report which will do the following Generate a concatenated class namenumber string, such as FIN 200 from something in the Finance Department where the course number is 200 Print that class namenumber string in the first column of your report Print the class description (such as Introduction to Finance) List the students

What SQL query can be used to find the VendorIDs that appear in both the top 20 and bottom 20 invoices, based on their total invoice amounts? (using CTEs and the INTERSECT join condition)

Connect C dbo.InvoiceArchive dbo.InvoiceLineltems dbo.Invoices dbo.Terms dbo.Vendors Columns VendorID (PK, int, not null) VendorName (varchar(50), not null) VendorAddress 1 (varchar(50), null) VendorAddress2 (varchar(50), null) E VendorCity (varchar(50), not null) VendorState (char(2), not null) VendorZipCode (varchar(20), not null) VendorPhone

Design an ER diagram with a drawing tool or neatly by hand. Briefly note and justify assumptions you are making about the problem domain. In your system, there are two classes of users, registered (logged in) users and visitors. Most of the features are geared towards registered users, who have signed up with your site and provided some

Find the best join order according to the dynamic programming algorithm used in System-R. You should give the dynamic programming table entries for evaluate the join orders. The cost of each join is the number of I/O accesses the database system performs to execute the join. Assume that the database system uses the two-pass sort-merge join

R(A,B,C) S(B,C) T(R)4000 T(S)3000 V(R,A)100 V(R,B)200 V(S,B) 100 V(R,C) 100 V (S,C) 300 W(B,D) T(W)2000 V(W,B) 100 U(A,D) T(U)1000 V(U,A)100 V(W,D)50 V(U,D)100

I want you to arrange these balls into two or three groups based on likeness. please 1) list the ball numbers assigned to each grouping, 2) describe the reasoning behind each of your groupings (like a taxonomist), and 3) describe the difficulties in creating those groups., 1 In the lectures this week you learned about clinal variation. Blood type,

Display student Marital Status based on a code." (Hint: Use Simple CASE in the SELECT clause. M = Married, S = Single, D = Divorced, W = Widowed) You can find the solution in CH19_Student_Marital_Status (18 rows).

Students Student D StudFirstName StudLast Name Stud Street Address StudCity StudState StudZipCode Stud AresCode StudPhoneNumber StudBirth Date StudGender Stud Marital Status Stud Major Majors Majori Major to Student Schedules Student D ClawD ClassStatus Grade Student Class Status ClassStatus ClassStatusDescription Class Rooms ClassRoom D Building

Use MySQL Workbench to make an EER diagram for a database that stores information about customers. Each customer must have an email address, first name, and last name. Each customer can have two or more addresses. Each customer can have a default billing address and a default shipping address. Each address must have a street address, city, state,

Find the top 3 road segments for each route in terms of ARP from df. Store the results in x. Include only four columns in x. SegID, RouteNo, ARP, [rank of that road segment in its route] Hint: use duckdb

SegID Route No BegMP AccCount 5 190.61 5 166.75 5 172.58 5 133.06 90 51.74 526 405 531 405 167 3319 3322 3112 3115 3174 3177 2810 2813 4386 4389 1366 1367 2302 2305 1617 1620 2305 2308 2456 2459 4.45 3.77 6.63 4.01 25.75 SPF EBSAFE 224 44.175417 220.331946 176.156529 196 18.312077 187.501295 169.189218 149 20.361963 143.439954 123.077990 126

Convert the ER diagram to a relational database schema. Make sure you underline the key for each relation 10 points. Name Name Founded Year Head Coach Number Country League Member Of H Team Sign Player Name Sport Home Field Salary Position Stadium Name 000 City Address Capacity

We need a stored procedure that needs to be executed whenever a customer places an order. The stored procedure checks the existing stock in the inventory db and decides whether the order can be placed or not based on the existing stock and the quantity requested. If the order can be placed, the requested quantity is deducted from the stock and the

you have an e-commerce website where customers can place orders for products. You have two tables in your database: orders and inventory . The orders table contains information about each customer order, such as the order number, order date, product number, quantity and total amount (we are limiting one product per order). The inventory table

What is the difference between structural dependence and structural independence in a database management system? How does this impact the design and operation of a database? 2.) What is data dependence and data independence, and how do they differ from one another? How can they impact the flexibility and scalability of a database management

WRITE SQL queries to do following from the given cruise database tables:(AND SHOW OUTPUT SCREENSHOT TO CONFIRM CORRECTNESS) mysql) 1.List of the cruise directors who have been on ships that weight less than 70,000 2.. List the names of passengers who have been on ships that weight less than 70,000. 3.List the cruise directors who have visited

The Cruise Scenario SQL query SELECT FROM SHIP SHIPNUM 005 008 009 012 020 022 025 028 11111 21664 23007 24288 26964 27045 28532 29191 29890 35218 37894 SQL query SELECT FROM CRUISE BUILDER Sea Joy Master The Spirit of Nashville Ajax Ocean TV Ajax Prince Al Ajax Queen Shirley Master Pacific Blue Ace Ace Ace CRUISENUM STARTDATE SHIPNAME Caracas

You have been asked to help maintain a database where you work. The DBA has approached you with a report that shows some mismatches. For instance in the CUSTOMER table, the CUSTOMERID 2010 belongs to Edward Hampton, however, the DBA knows for certain Edward Hampton has not placed any orders. However his CUSTOMERID is showing up in the ORDER table.

MySQL Sample Database Diagram Run the following queries to create the complete database. Check the order in which you need to create the database. Make sure to consider the Primary and Foreign Key constraints

TH productlines productLine textDescription htmlDescription image employees employeeNumber lastName firstName extension email officeCode reports To job Title offices officeCode city phone addressLine1 addressLine2 state country postalCode territory HHH products productCode productName productLine productScale productVendor product Description

Using SQL, add a foreign key constraint to the Rides table. If a Drivers id is deleted in the Drivers table, the Drivers id is also deleted in the Rides table.

Customers cid Drivers did Rides rid fname Iname phone zipcode fname Iname cid did Ride From Ride To cost Rating

Use the SQL Server Management Studio to complete this lab. a. Create a SQL script file named Car InspectionDBScript Yourinitials.SQL (John Doe would name it Car InspecitonDBScript JD.SQL). When executed this script is to create a database named Car Inspection Your Initials that contains the objects described in this document. 2. Upload and submit

Write a query to find each customer who has an account at every branch located in Brooklyn Consider the bank database schema given below, where the primary keys are underlined. Write the SQL DDL corresponding to this schema (i.e. the CREATE TABLE statements). Make any reasonable assumptions about the data types. Be sure to declare primary and

Add a new column for the 2-digit contact code in the PUBLISHER table. 2. Add a foreign key constraint between your contacts table and PUBLISHER table.

CUSTOMERS Customer Lastname Firstname Email Address City State Zip Referred Region ORDERS Order Customer Orderdate Shipdate Shipstreet Shipcity Shipstate Shipzip Shipcost ORDERITEMS Order Item ISBN Quantity Paideach BOOKS ISBN Title Pubdate PublD Cost Retail Discount Category BOOKAUTHOR AUTHOR AuthorD Lname Fname Gift Minretail Maxretail ISBN

Turning the ERD into tables. You may choose your implementation details. Be sure to use foreign key constraints and unique where necessary. Be sure to explain your choices.

userid email password First Name Last Name Street Address City State Zip users. PhoneNumber Accepts Text Order History selects. Is selected by Menu Menu Item Size Allergen Info is created by Menu Item Name Menu Item Description Price Creates Consists of is order on User Order Date and Time Location Location City Location Zip Location Phone Number

Draw ERD using crowfoot notation, create a table including an identity, creat a foreign key constraint

i Treatment invoice Bay Animal Clinic P.O.Box 649 Montrose, AL 36559 For Suzanne Pardue 34 Hannon Ave Mobile, Al 36604 Services rendered by Babette Dixon, DVM Type Description Procedure Annual Exam Injection Feline Leukemia Booster Fvrcp Booster Injection Urinalysis with Sedimenta Lab work 7 Qty 1. 1 1 Reminders for Echo (Weight 12.2 lbs age 4

Consider all three tables together, how many primary keys AWS contains? 13. How many foreign keys AWS has in total? 14. Which table(s) contain a foreign key? 15. Suppose ON UPDATE CASCADE is included in a foreign key constraint of AWS. Explain its actual meaning and implication in AWS.

Client ClientID LastName FirstName Street City State ZipPostalCode Country AreaCode PhoneNumber Email 00 Sales DateAcquired Acquisition Price Asking Price DateSold SalesPrice ClientID WorkID bo O Artwork WorkID Title Copy Medium Description ArtistID

When implementing a relational database schema in SQL, does it matter in which order you create tables? Hint: suppose that foreign keys exist. (b) Explain the options available for enforcing a foreign key constraint when creating a foreign key, and what effects they have. What is the default option?

Use the relational schema and functional dependencies (FDs) in Figure 1 to answer the following questions. Figure 1. HospitelPatient (hospitalname, hospitaldistrict, hospitalprovince, hotelname. hoteldistrict, hotelprovince, patientname, citizenid. patientappid, admitteddate, checkoutdate, symptomcode) hospitalname hospitaldestrict,

Write the Create table command for the schema with primary and foreign key constraints (3+2) Member(memb_no,name,age) Book(isbn,title,authors,publisher) Borrowed(memb_no,isbn,date) i. Find the title, authors and publishers of the book. ii. Find the names of members who have borrowed all books published by "Mc-Graw-Hill"

Your mydb schema should be similar to this design: Department table: deptNumber, deptName Employee table: employeeSSN, empName, address, birthday, salary, deptNumber Dependent table:dependentSSN, name, sex, birthday, employeeSSN Project table: projectID, projName, deptNumber employeeProject table: employeeSSN, projectID

Add referential integrity between a. Department table and Employee table, add foreign key constrsint in the Employee table. b. Employee table and Dependent table, add foreign key constrint in the Dependent table c. Department table and Project table, add foreign key constraint in the Project table. d. Employee table and EmployeeProject table, add

Following code is generating an error message I can not resolve. Please help. The error message is posted above the code. Error Code: 1215. Cannot add foreign key constraint 0.375 sec CREATE TABLE QUALIFIED (FacultyID INT, CourseID VARCHAR(8) , DateQualified DATE, CONSTRAINT QUALIFIED_PK PRIMARY KEY (FacultyID, CourseID), CONSTRAINT QUALIFIED_FK

JUST NEED ANSWER

What would be the most appropriate MySQL mechanism to employ to ensure that the budget of a Dept is always at least 10000

Create three tables a. Customer b. Rent C. Film . Define appropriate data type and data size Define column constraints Specify IntegrityDomainPrimary KeyReferential Integrity constraints a. Primary key b. Foreign key CONSTRAINT rent fk c FOREIGN KEY cid REFERENCES customer ON DELETE CASCADE c. The price of a film ranges from 0.50 to 19.99 d. A

For the presidential election, votes are recorded electronically using the following ER model. Write down the tables for the ER model. Identify foreign keys and write down the necessary CREATE TABLE statements with primary and foreign key constraints 10 points. name state Voter SSN N votes for name 1 party Candidate

Modify the tables in the Reporting_Schema to prepare them for reporting. Create a script to do the following. Save your script with the name, "Modify Warehouse". Include a screen snip of your completed script in your solution file. Alter the three tables to set up a primary key constraint for CustomerID in Customer, SalesOrderID in

Buse FVTCSalesDB. EIF EXISTS (SELECT FROM dbo.sysobjects WHERE id OBJECTID(N reportingschema. customer) AND OBJECTPROPERTY ( id, NIsUserTable) 1) DROP TABLE reportingschema. customer select into reporting schema.customer from saleslt.customer BIF EXISTS (SELECT FROM dbo.sysobjects WHERE id OBJECTID (N reportingschema. salesorderheader) AND

Create a new database with the name Lab08Exercise. Use Lab08Exercise to accomplish the below tasks. Create a table Faculty based on the following chart Data type Constraints INT (6) Primary Key Last Name Varchar(15) First Name Varchar(15) Dept Char(3) Column Facultyid Not NULL Dept Code Dept Name Not NULL Create a table Dept based on the following

. Add 2 foreign key constraints to the songs table artistid and genreid are the foreign keys. 15.1. You may use either one or two statements to add the constraints.,ysql select from songs id title 1 Lover 3 Maps 4 Under Pressure 5 Blue Danube 6 Misery 7 Her Majesty 8 Revolution9 9 A KindOfMagic 10 11 Broadway I Hope You Dance 12

following SQL statements, some tables are created with different constraints. Give two foreign key constraints and specify in which table each is given CREATE TABLE Customer T (Customer D CustomerName Customer Address CustomerCity CustomerState NUMBER(11,0) VARCHAR2(25) CustomerPostalCode CONSTRAINT CustomerPK PRIMARY KEY (CustomerID)) CREATE

Why do I have this error at the end? Everything seems to be typed right but it still gives me the foreign key constraint error? mysql> create table vendor ( vname varchar(30) primary key, address varchar(100)); Query OK, 0 rows affected (0.29 sec) mysql> mysql> create table department ( -> dname varchar(30) primary key, -> phone char(10) not

2. 3. Use the create table statement to create a table named dogs with the following columns and data types name age weight ownername varchar(20) int int varchar (50) Add the following check constraints to the dogs table the age must be greater than 0 the weight must be greater than 0 Create a table named owners with the following columns and

Give the SQL definitions of the tables Cross CountrySkier(Name, Country, Age) Competes (SkierName,ContestName, Contest (Name, Place, Country, Length) Showing particularly the foreign key constraints of the Competes table. Placement)

Modify the EMPLOYEE table so that JobClass column can store data up to two characters only. Modify the EMPLOYEE table so that EndDate column is deleted Create a new Table BOOK_PRICING containing 3 columns ISBN, Cost, CatCode by selecting these columns from the existing BOOK table. In the Book_Pricing Table, add the primary key constraint on the

Text file containing SQL create table statements based on the schema for the scic database. Make sure to define primary key and foreign key constraints in the create table statements.

In order to implement the database design that follows, how many FOREIGN KEY constraints would we need to include in the SQL for implementing the entire DB (all tables) REGION RegionID RegionName STORE StoreID StoreZip RegionID (FK) PRODUCT ProductID ProductName ProductPrice VendorID (FK) CategoryID (FK) SOLDVIA ProductID (FK) TID (FK) NoOfitems

Write down the CREATE TABLE query to create the WORKSON table. (Include primary key and foreign key constraints in the create table query),Consider the following database schema of the Company database. EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Superssn Dno DEPARTMENT Dname Dnumber Mgrssn Mgrstartdate DEPTLOCATIONS Dnumber Dlocation