Answered step by step
Verified Expert Solution
Question
1 Approved Answer
2. (30points, each 5 points)Consider the detection relational database for a detection enterprise with 3 relations as follows: project(projectcode, createdate, projectname) projectcode:code of a project,
2. (30points, each 5 points)Consider the detection relational database for a detection enterprise with 3 relations as follows: project(projectcode, createdate, projectname) projectcode:code of a project, createdate: when the project was created in system; projectname: project name; projectcode is the primary key of this relation. contact(contactcode contactname, amount, startdate , enddate, projectcode), contactcode: contact code: contactname:contact name; amount: the total amount for this project; startdate: the beginning date of this contact; enddate: the enddate of this contact; projectcode:the project code which this contact belongs to is a foreign key sample(samplecode,samplename, tensilevalue, testtime contactcode), samplecode: the sample code; samplename: the sample name; tensilevalue:the tensile value for this sample; testtime: test time for this sample; contactcode: the contact code which this sample belongs to is a foreign key. A project has possibly several contacts and a contact belongs to one sole project. One contact may have several samples and a sample belongs to sole contact. The primary keys are underlined by solid lines. Please give the SQL statements for the following problems: (1) Create table contact with suitable data type for each attribute. You are required to define contactcode as the primary key and projectcode as the foreign key which references to project (projectcode). (2) Retrival the contactname which containing the letter 'v'. (3) Retrival the projectcode and projectname which is created before "2016-01-01". (4) Retrival each project code and its total amount according to contact relation. (5) Retrival sample name, tensilevalue and corresponding contact name which belongs to the contact with code 2020101. (6) Delete all the sample rows which belongs to the contact with code "2020102
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started