–SECTION 3– This section contains 3 questions, in total a…
–SECTION 3– This section contains 3 questions, in total are worth 45 points. They are related to each other. The questions are meant to test your basic SQL abilities (advanced DDL statements and understanding underlying data structures given set of business requirements). You must write your responses in the boxes provided.
Read DetailsInclude a new column in a table… You were tasked with upda…
Include a new column in a table… You were tasked with updating an existing table called InvoiceLineItems to include a new column that allows Purchasing Specialists in the Finance department to track the tax percentage of each line item that was purchased. If a receipt does not have a listed tax rate for the line item, they want it to default to 8% for later query calculations and therefore it cannot be zero. Write a SQL statement that includes this new column and supports the end result the Purchasing Specialist is looking for. Choose the appropriate data types and column conditions (HINT: You will need more than one constraint).
Read DetailsEstablish a relationship between tables… In the previous 2…
Establish a relationship between tables… In the previous 2 questions you added a ClientProfile table to your client management system database to track basic client information and populated it with the initial profiles. Now you need to create a second table that tracks services provided to those clients. Each client may receive multiple services, but each service record should belong to a valid client profile. The program needs to track: A unique identifier for each service record The client who received the service The date the service was provided The type of service provided The only service types that should be allowed are: Case Management Transportation Referral Write a SQL statement that makes a table called ClientServices under the same schema as ClientProfile. HINT: You should only have 4 columns in your table. Make sure your table includes appropriate: Column names Data types Column definitions, including any constraints (table or column level) Relationships
Read DetailsEnter a value in the box provided… Consider this SQL code…
Enter a value in the box provided… Consider this SQL code below. What value would be returned for the ThirdValue. CREATE SEQUENCE dbo.seq_demo1 START WITH 50 INCREMENT BY 25; SELECT NEXT VALUE FOR dbo.seq_demo1 AS FirstValue; SELECT NEXT VALUE FOR dbo.seq_demo1 AS SecondValue; ALTER SEQUENCE dbo.seq_demo1 INCREMENT BY 10; SELECT NEXT VALUE FOR dbo.seq_demo1 AS ThirdValue; SELECT NEXT VALUE FOR dbo.seq_demo1 AS FourthValue;
Read DetailsMake a new table… You are on a project team with two other…
Make a new table… You are on a project team with two other data engineers. Your team has been tasked with creating a new table for a client management system. The program that will use the system provided the requirements below. Requirements Gathered: Need to uniquely identify each client NOTE: They mentioned the need to get distinct counts of clients in reporting. Need to collect client’s name NOTE: They mentioned the need to sort by last name sometimes in reporting. Need to collect client’s date of birth NOTE: They mentioned the need to calculate age in reporting. Need to collect client’s home address NOTE: They mentioned the need to sort by city sometimes in reporting. Need to collect client’s race NOTE: They mentioned that they can only report on these options: Asian American Indian or Alaskan Native Black or African American Native Hawaiian or Pacific Islander White Multi-racial Need to collect client’s ethnicity NOTE: They mentioned that they can only report on these options: Hispanic or Latin(a)(o)(x) Not Hispanic or Latin(a)(o)(x) Need to collect client’s sex NOTE: They mentioned that they can only report on these options: Male Female Intersex Prefer not to answer Need to collect the date the profile was added to the database NOTE: They do not want to enter today’s date every time they create a profile. NOTE: The client’s date of birth cannot be on or after the profile’s creation date. Need to collect notes (comments) for intake processing NOTE: They don’t often take notes, but when they do, it is short (no more than 10 or so words). Write a SQL statement that makes a table called ClientProfile under the DBO schema. Make sure your table includes appropriate: Column names Data types Column definitions, including any constraints (table or column level)
Read DetailsEnter a value in the box provided… Consider this SQL code…
Enter a value in the box provided… Consider this SQL code below. What value would be returned for the FourthValue. CREATE SEQUENCE dbo.seq_demo1 START WITH 10 INCREMENT BY 10 MINVALUE 10 MAXVALUE 30 CYCLE; SELECT NEXT VALUE FOR dbo.seq_demo1 AS FirstValue; SELECT NEXT VALUE FOR dbo.seq_demo1 AS SecondValue; SELECT NEXT VALUE FOR dbo.seq_demo1 AS ThirdValue; SELECT NEXT VALUE FOR dbo.seq_demo1 AS FourthValue; SELECT NEXT VALUE FOR dbo.seq_demo1 AS FifthValue;
Read Details