Databases Questions and Answers August 20, 2021

CREATE TABLE EmployeeWork (age INTEGER, ssn CHAR(10), name CHAR(50), pid CHAR(10) Not Null, PRIMARY KEY (ssn), FOREIGN KEY (pid) REFERENCES Project ON Delete CASCADE) CREATE TABLE Project( pid CHAR(10), Project pid 100 102 200 101 budget 500K 400K 700K 100K budget CHAR(10), PRIMARY KEY (pid)) Employeework Ssn name 5380 Joe 5381 Ali 5382 Jill pid

Write the SQL-DDL statements, to create the following tables for the ER diagram and capture as many of the constraints as possible. Please specify your decisions with the foreign key constraint. We assume covering constraint is not true; this means some items in M set are not in O set.

e M isa O b R S N DO P d h

Create a stored procedure named spPayIncreaseListing that returns the following columns: FirstName LastName HireDate (use a CONVERT function so HireDate appears as 05/22/12) PayRate PayRateStartDate (use a CONVERT function so StartDate appears as 05/22/12) PayRateEndDate (use a CONVERT function so EndDate appears as 05/22/12) PayComment (use a

Write a C function program to find out the n th terms, , and sum, , of Arithmetic Progression (A.P) series. Let the first term be a, number of items, n and the common diference, d of A.P. Then call the function in main, in which the parameters were key in from the keyboard and print out the results. Create the function with return multiple output.

Explain in detail the steps for revising the R tree when inserting a new point m,Given a set of 2D points from a to | and an R tree constructed on top of the data points

2 es E e 9 b 3 h ez C e6 eg 244 1 2 05 R d 185 16 e2 es b C 146 C 06 07 0 hf 167 22 kj Ma

Display query that selects records from the Employee Dimension table where the employee key is greater than 100 but less than 250 or whose last name does not begin with the letters C through L. Display query that selects (from the Product Dimension table) the product key, product name and color columns for all products that have a color of

1. Display query that calculates and imports into Power BI the average salesAmountQuota per YEAR for each year since the year 2000 (or earliest valid year), using data in the factSalesQuota table. Display only the calendar year and the average salesAmountQuota 2. Use the HAVING clause to display query that calculates and imports into Power BI the

Create a View (V_EMP_DEPT_PROJ) statement that joins the EMPLOYEE and EMPPROJACT (join on EMPNO) and PROJECT table (join on PROJNO). Limit the view to contain : o ID o Name (FirstName and Lastname) o WorkDept o Salary o ACTNO o PROJNO o PROJNAME o Where Salary < 100000

contacttype id INT(11) label VARCHAR(255) Indexes contact pid INT(11) ctid INT (11) value TEXT Indexes person id INT(11) firstname VARCHAR(255) lastname VARCHAR(255) dob DATE Omanagerid INT (11) notes TEXT created TIMESTAMP Indexes projectperson prid INT(11) pid INT(11) rid INT (11) Indexes role id INT(11) label VARCHAR(255) Indexes project id

Find sailors with a higher rating than all sailors younger than 21 A. Write a SQL query to answer the question using OP ALL B. Write a SQL query Close (CtrlX) question using NOT EXISTS C. Consider the following instance of Sailors. What are the results of running the query in A and B. SID 1 2 3 4 5 SNAME RATING AGE Dustin 7 Zorba 10 Rusty 5

sequence. Convert the following SQL query to a relational algebra SELECT S.sname FROM Students s WHERE s.sid NOT IN (SELECT e.sid FROM enroll e WHERE e.semesterFall22)

Display a function that computes a 3.5% raise for an employee. The employee salary will be passed as a parameter. Be sure to explicitly code the parameter mode. Code one exception that handles any types of error and returns NULL. Reminder: There are only 2 decimal places acceptable when displaying currency. Table : employees. Function name :

103 EMPLOYEE ID 222 223 104 107 5 rows returned in 0.01 seconds Alexander Chen Alain Bruce Diana FIRST NAME Downl Hunald Li Fontaine Emit Lorentz LAST NAME 9000 8000 7800 6000 4200 SALARY 9315 8280 8073 6210 4347 FUNC EXAM2(SALARY)

isplay the country id, name and area for all countries in region 5. Display the number of countries listed at the bottom of the list. Order the result set from the country with the largest area to the smallest area. Use a cursor. Table : countries

55 54 51 57 591 58 56 595 593 592 598 8511965 2766890 1285220 1138910 597 594 500 280 1098580 912050 756950 406750 283560 214970 176220 163270 91000 12173 3903 Country count Federative Republic of Brazil Argentine Republic Republic of Peru Republic of Colombia Republic of Bolivia Bolivarian Republic of Venezuela Republic of Chile Republic of

Create a new database that holds the same tables and data as Question 2as solution from the Midterm Review Questions (which I put below this class activitys details for your convenience). Put the query you use for each task in a word document and submit it. Create Tables (create the same tables from Question 2as solution). Dont forget to correctly

Develop and test the following stored procedures. A procedure that accepts an employee number and lists all information for that employee. Display a message if the employee number is invalid. A procedure that accepts a date as a parameter and displays the employee number and first and last names for all employees who were born on or after that

Provide SQL statements and the query output for the following No 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Question Display all the customers having full names longer than 10 characters. List all the names of the customers if their names contain the string ON, ignoring the case. Which plan has the highest number of cancelled phone numbers How many

Draw an Entity Relationship Diagram (ERD) of the proposed Barwon Health Prescription System based on Crow's Foot notation using a professional drawing tool Based on the below scenario, Prescription System for Barwon Health, Geelong The prescription branch of Barwon Health is facing a rising cost and looking into ways that could help reduce

Create the a relational schema for the GC Inc Operations Database based on the following requirements. The GC Inc Operations Database will keep track of the information about consultants, contract managers, customers, supervisors, and contracts. For each consultant, the database will keep track of a unique consultant id, the consultant's first

Create one.sql file with SQL queries that executes the requirements noted below. Make sure you have comments in your sql in the beginning providing your name, coursecourse section, and CWID. (points deducted for not having this comment) 1. Use the salesordersDB database schema. 2. Translate the requirements below into a SQL query that accomplishes

Create a PISQL function named AVGSAL that will return the average salary from the employees table. You must develop your own average function. You cannot use the inbuilt min function. Use cursor to fetch the records from employee table.,Create a PISQL function named Check LName, that will check whether the name is present in the last name column

Consider the following schedules. S: W1(X); R3(Y); R2(Y); W2(X); R2(X); R1(Y); R1(Z); R2(X); R2(X); W3(Z); W2(Z); C3; C2; C1 Draw the precedence graph for the schedule. Is the schedule conflict-serializable? Is the schedule recoverable?

Write SQL queries, use at least one view for each query. Then, evaluate the queries on the cruise tables (and views) in MySQL database, and copy the answers here. List the directors who have worked on ships built by Ajax. use the view CruiseInfo created above in the previous question. List the countries that have been visited by at least one

Explain how to import and setup your database. Please explain also if any code needs to be changed in the database script to make sure it properly added into the user system. You will also explain how the user can bring the query file and run it using their SSMS tool and Database server (Assume user already have Database server and SSMS tool setup

Why is data modeling an important tool in database design? What problems can occur if we neglect modeling? What modeling tools does our textbook discuss and how are they used effectively?

Create a LARGEPROPERTY table with the structure shown in Figure COLUMN OFFICENUM ADDRESS BDRMS FLOORS MONTHLY RENT OWNERNUM LARGEPROPERTY Table Layout TYPE DECIMAL 2 LENGTH DECIMAL PLACES NULLS ALLOWED DESCRIPTION No CHAR DECIMAL DECIMAL DECIMAL CHAR 25 2 2 6 0 0 2 No Management office number (primary key) Address of property (primary key) Number

Design conceptual model of a database using ER Diagram for the following scenario Uber Eats business Rules. UberEATs is an online platform for food ordering services. Uber eats partner with restaurants and independent delivery persons to provide food services to the end customers. Customers need to provide their email address or mobile phone

How many documents are there with the value Tambra Mercure for the name field? How many documents were updated? Run the following statement that will add an array for bonus quizzes to the student with _id 5 / name Wilburn Spiess. db.students.update( { _id: 5 }, {"$set" : {"bonus_quizzes" : [ { "quiz": 1, "score" : 100 }, { "quiz": 2, "score" : 80

Create a table with three columns: the forms of government, how many there are of each, and their life expectancies. Rename the first column 'Form of government' the second column and the third column 'Life Expectancy' Sort in descending order by life expectancy.

Design a database for Professional Baseball. '1'our diagram must contain all the tables needed to meet the business rules below. For ea ch table, you must show the primary keys, the relationships to other tables, cardinality and if there is total or partial participation between the entities. '1'our diagram should also include any attributes

Create table that shows the average GNP of each continent rounded to two decimal points. Remember to display the column continent and rename the column displaying the average to "GNP"

Display the supplier name (SupName) and purchase date (PoDate) for all suppliers despite if an order was ever purchased through the supplier or not. Sort the results on supplier name in alphabetic order. Display ONLY the supplier name (SupName) who has the most orders in Purchase_Order table.

PF SUPNR PF PRODNR P P PURCHASEPRICE DELIVPERIOD F SUPPLIESPK (SUPNR, PRODNR) SUPPLIES IBFK1 (SUPNR) SUPPLIES IBFK2 (PRODNR) SUPNR SUPNAME SUPPLIES SUPADDRESS VARCHAR2 (4 BYTE) VARCHAR2 (6 BYTE) PONR PODATE NUMBER (8,2) NUMBER (11) SUPCITY SUPSTATUS SUPPLIERPK (SUPNR) SUPNR SUPPLIER PURCHASE ORDER VARCHAR2 (4 BYTE) VARCHAR2 (40 BYTE) VARCHAR2

Create a database named Animals. Inside the database create a collection named MyPets Translate the following tables into JSON documents. Insert the documents into your collection Use the .find() function to show that the documents have been stored in the database Consider using the ObjectID for the primary key. There may be some room for

Pet Owners RecordID 1 2 3 4 5 6 Last Name Simpsons Simpsons Smits Peters Partridge Wanderer First Names Homer Marge Lisa Bart Homer Marge Henry Lucy Philip Jane Address ID 1 1 2 3 4 License ID 1 2 3,4 5 6

List the first and last names of all owners who own a two-bedroom property. Use the IN operator in your query. List the square footage, owner number, owner last name, and owner first name for each property managed by the Columbia City office. List the square footage, owner number, owner last name, and owner first name for each property managed by

Your program will take a file name from user and find out following information and display it. Number of lines, number of vowels, number of consonants, and number of numerical characters Also, ask user if user wants to repeat and depends on the answer, your program needs to terminate or repeat. If user enters wrong information your program needs

Giffords is committed to its customers, and therefore wants to present their allergen information in a sorted way so that items with the same allergen profile will be together in the representation. Choose an order of the allergens and sort the Flavors table in that way. Within the group with the same allergens, sort the names ascending. At the

Give an example of the following concepts. Data Definition Language (DDL) statement Data Manipulation Language (DML) statement Schema Data model Instances Domain constraints Business Rule constrains Data dictionary Transaction View Casual end user Parametric End user Sophisticated end user Primary Key Attribute Foreign Key Attribute Unique Key

Over the last few decades Accounting Information Systems have evolved through many stages of evolution; list all those stages of evolution and briefly describe the defining characteristics of each. Define and elaborate the concept of User View? Define and Distinguish between the Online Analytical Processing (OLAP) and Online Analytical Processing

Discuss data identified which would be suitable for storage in a data warehouse. An example and brief discussion of one suitable data schema. E.g. A star, snowflake or galaxy schema Using examples from your scenario(Hilton Hotels) discuss: The ETL process and Online Analytical Processing (OLAP) with the schemas

Explain why descriptive analytics would be beneficial for the sales and marketing functional area of Boeing. Provide an example of how each of the following BA (business analytics) tools could be used by the sales and marketing functional area of Boeing: (i) Online analytical processing (OLAP) also known as multidimensional analysis (ii) Data

State FOUR (4) requirements for the web and database management system integration. Draw a common architecture for integrating the web and database management systems. Describe the following in the perspective of database system. (i) Online Analytical Processing (OLAP) (ii) Data mining

Describe at least two (2) methods that business owners could use in order to protect the privacy of both personal information and organizational data resources related to the Web and information technologies. Provide one (1) example of the use of each method to support your response. Use the Internet or the Strayer Library to research articles on

Explain the terms On-Line Transaction Processing (OLTP) system,Dat aWarehouse (DW) and Data Mart (DM). Critically compare the purpose of these, their data requirements and utilisation scenarios. Using the definitions of Business Intelligence, Online Analytical Processing (OLAP) and Data Mining explain how analytical capability embedded in ones

What distinctive characteristic of Online Analytical Processing (OLAP) enables users to gain fast, consistent, interactive access to a wide variety of possible views of the data? What type of data model supports this method of accessing data?

Designing an Online Analytical Processing (OLAP) system, you have to choose between multidimensional and tabular designs. In what scenarios might you choose a multidimensional model and what situations might you choose a tabular design?

Show how the query plan shown below is optimized by the DBMS based on query optimization using heuristics The initial query plan generated by the SQL query parser of the DBMS is show below Query: SELECT Lname FROM EMPLOYEE , WORKS_ON, PROJECT WHERE Pname=Aquarius AND Pnumber=Pno AND Essn=Ssn AND Bdate > 1957-12-31;

(a) EMPLOYEE Lname PnameAquarius AND PnumberPno AND EssnSsn AND Bdate19571231 1 WORKS ON PROJECT

Performance transparency in a distributed database system may be achieved through optimization of query statements. Give an example of a query statement that illustrates this type of optimization.

Write about your plan for the Database Performance Tuning, Query Optimization and Distributed Database Management Systems. Include appropriate diagrams. Submit the document to the professor via the online class

Write the 5 different queries from the same tables and rows. This can use different indexes, joins, aggregates, merges, parallel/serial, etc. Then force a better query plan using hints Here are the instructions: -Create a database with some tables, insert some rows, and select 5 different queries. -Save screenshots of the query plans generated by

The relationship between Customer No and Friend No was set up using a Query by Example (QBE) Graphics User Interface. To set up this relation, the QBE software required that a duplicate of the customer record was shown on the screen. This is shown as Customer Record_1 in the diagram below. You have been asked to make all the necessary changes to

Customer Record Customer No Name Address Friend No

Using Oracle, practice writing and testing Data Definition Language (DDL) and Data Manipulation Language (DML) SQL statements. You may use Oracles SQL Plus, SQL Developer, or Oracle Data Modeler tools. Create tables to fulfill the requirements of the ERD below. Populate the newly created tables with three records of data and write database queries

CUSTOMER Customer ID Customer Name Customer Address (Street, City, State, Zip Code) Customer Telephone Has Location LOCATION Location ID Customer ID (FK) Location Address (Street, City, State, Zip Code) Location Type HH Time of Day Is Charged RATE Rate Class Location ID (FK) RatePerKWH

Consider the following tables: team_table team rank country Alpha 1 USA Bravo 2 KOR Charlie 3 MEX country_table country_code country_name language USA United States of America English KOR Republic of Korea Korean MEX Mexico Spanish language_table number language cardinal ordinal 1 English One First 2 English Two Second 3 English Three Third 1

Create a Customer table Insert the data in the Customer table. Update customer contact name to John Smith, who is name Wilman Kala. Write a SQL statement to display all the information of all customers. Write a SQL statement to display specific columns like name and address for all the customers. Write a SQL statement to display names and city of

Database trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. Provide ONE (1) exampleof database trigger.

Before DBMS, applications were directly built on top of file systems. Give two disadvantages of this approach. 2- What would happen for the external applications if the logical schema were dependent on the physical schema? 3- Data Manipulation Language (DML) can be divided into two types. a. What are these types? b. Which one is easier to learn?

An Oracle database transaction involves one or more statements consisting of one data definition language (DDL) that is responsible for the implicit ending of transactions and at least one or more data manipulation language (DML) that constitutes atomic database changes. There are many ways to interface with an Oracle database. Name one way to

Insert the following rows to the Student, course and study tables. Insert into student values (20150263, Omar Thabit, M) Insert into student values (20150392, Mahmoud Nafiseh, M) Insert into student values (20160088, Marwan Alkhasawneh, M) Insert into student values (20160122, Yousef Suleiman, M) Insert into student values (20160730, SARA AL

Based on this implemented physical schema diagram create database. Also, use Data Manipulation Language (DML) to add (insert), delete, and modify (update) data. --Selecting the database Use EZRentalDB Testing Tables. --Tables used and order to test This table are parent #1 1. DriverLicense 2. CustomerUserAccount 3. CreditCardMerchant 4. Company 5.

Credit Card Merchant 8 Column Name Data Type Allow Nulls MerchantCode tinyint MerchantName varchar(50) ko Credit Card 8 Column Name Credit CardNumber CreditOwnerName Credit CardIssuing Company MerchantCode ExpDate AddressLine1 AddressLine2 City StateCode ZipCode Country Credit Card Limit Credit CardBalance Activation Status Data Type varchar(16)

Programming language, such as JAVA, C, Python, and SQL, are commonly used in electronic health records and databases. Relational databases use structured query language (SQL) programming. SQL statements can be divided into two categories: data manipulation language (DML) and data definition language (DDL). Compare and contrast data manipulation

Suppose that there are three tables, Person, WorkFor, and Company, in a database. o The Person table stores information about the ID, name, street, and city of each employee. o The Company table stores information about the tax number, name, and city of each company. A company may have several branches located in different cities. In this case,

Write the SQL to create a table based on one of your entities. Be sure to identify the primary key as part of your SQL syntax Create a view. Write the SQL to create a view on the base table Show the base tables with data. Show a base table along with six rows containing data for three entities in your entity-relationship diagram. (Display each

1. Data Definition Language (DDL) is used at the physical design step of the Database Lifecycle. 2. Drilldown involves analyzing a given set of data at a finer level of detail. 3. An OLAP cube is created by converting the relational format into a dimensional format. 4. The following two SQL statements will produce different results. SELECT

Create data types for attributes. Remember that your COURSE_ID should be alphanumeric or VARCHAR (not numeric). You must use primary and foreign keys in your Data Definition Language (DDL) to create table statements. Do not plagiarize this assignment. Any teams creating non-unique SQL code from sources other than your own will receive a 0 for this

1) Create Oracle database tables using SQL Data Definition Language (DDL) for each table listed in the metadata of Project 2. You may need to use a combination of DROP TABLE, CREATE TABLE, and ALTER TABLE SQL statements. Make sure that entity and referential integrity are enforced by declaring a primary key for each table (these may be composite

You are provided with the specification requirements below to implement a database management system (DBMS) for Bank X to store details about their Branches, Accounts, and Customers. SR1 A BRANCH has a Brensbip that is numeric, a Branch Name, and Branch Address. The Brenship is unique for each branch while Branch Name and Branch Address may not be

Create the internal model. That is, write data definition language (DDL) commands to create each entity/table based on the specifications illustrated in the ERD diagram and the following additional specifications:

meal PK mealid name mealdate mealtotal carbohydratetotal PK fattotal proteintotal contains mealitem PK, FK1 mealid PK, FK2 item id quantity is for item item id description price HH contains K item macronutrient PK, FK1 itemid PK, FK2 macronutrientid gramamount corresponds to H macronutrient PK macronutrientid description calorieamount

Explain the following terms 1. Data 2. Mini World 3. Database System 4. DBMS 5. DBA 6. End User 7. Business Analyst 8. Data Model 9. End User 10. Atomic Value 11. Domain 12. Key 13. Foreign Key 14. Relation 15. Attribute 16. Tuple 17. Entity Integrity Rule 18. Logical Data Independence 19. Data Definition Language (DDL) 20. Data Manipulation

Using the ConstructCo database, you will modify the four tables with primary key and foreign key constraints. You will save your SQL code in a text file and submit. Write the SQL code to modify the table structure for the table named Employee using the following data dictionary.,Attribute (Field) Name EMPNUM EMPLNAME EMPFNAME EMPINITIAL

Consider a relational schema for storing information related to movies Actor(name, YofB, countryofbirth, bio) Director(name, YofB, countryofbirth, bio) Producer(name, YofB, countryofbirth) Country(code, name) Movie(title, year, genre, budget, grossearnings) DirectorMovie(d name, d YofB, mtitle, myear) ActorMovie(a name, a YofB, m title, m year)

Write a query that returns the following columns: Person's full name. Person's role at the school: principal, teacher, or student. Person's salary (or N/A for students). Person's school name.

SYSTEM.SCHOOLS P SCHOOLID SCHOOLNAME ADDRESS CITY REGION POSTAL CODE COUNTRY NUMBER VARCHAR2 (35 BYTE) VARCHAR2 (60 BYTE) VARCHAR2 (15 BYTE) VARCHAR2 (15 BYTE) VARCHAR2 (10 BYTE) VARCHAR2 (15 BYTE) F PRINCIPALID NUMBER SCHOOLSPK (SCHOOLID) SCHOOL PRINCIPALFK (PRINCIPALID) SYSTEM.PRINCIPALS IP PRINCIPALID F PERSONID SALARY NUMBER NUMBER NUMBER

Perform normalization by writing UNF, 1NF, 2NF and 3NF for the following user views. Please provide the answer in both table and schema form for each (UNF, 1NF, 2NF and 3NF) StorelD S101 S101 S101 S102 S102 S103 S104 LocationName North York North York North York Toronto Toronto ManagerID M200 M200 M200 M300 M300 Scarborough M400 Scarborough M400

Implement a virtual database on DBSEC schema, for example, on CUSTOMER/CUSTOMERS table(depending on how your spelled it). Your mission is to develop a single SQL script that will perform all the following tasks(scripts are already attached here): Please submit a screenshot of the final output. Total 5 steps: Drop Customer/Customers table and

Make an asp.net Models for the following entity. Below is the database diagram

Key Key Key RoleAccount Roleld Accountid Status Role Id Name Status Key Key Invoice Id Name Created Status Accountid Account Id Username Password FullName Email Status Address Phone Key Photo Id Name Status Featured Productid Key Key Key Product Id Name Description Details Status Quantity Featured InvoiceDetails Invoiceld Productid Price Quantity

CREATE DATABASE IF NOT EXISTS SCAD; USE SCAD; -- Basic Pilot Information CREATE TABLE Pilot ( license VARCHAR(10), pname VARCHAR(50), phone CHAR(10), miles int, CONSTRAINT Pilot_PK PRIMARY KEY(license) ); -- Basic Aircraft Information CREATE TABLE Aircraft ( id VARCHAR(10), make VARCHAR(25), model

Using the Shipper Drug Stores narrative, design and document a logical data model using Erwin. You should also make and document any reasonable assumptions where you feel the case is vague or ambiguous or missing any data necessary to meet the requirements. However you cannot ignore or overrule the requirements that are explicitly stated. Show all

Create a stored procedure named PRCPICHOURS that takes an employees EMPNUM as input and updates the newly added PILPICHRS column in the PILOT table (from Q.4 above) with the total hours flown for all charters in which the employee has been part of the crew as a Pilot. Hint Use the CHARTER tables CHARHOURSFLOWN to update the PILOT tables PILPICHRS

List the trip name of each trip that has the type Biking and that has Rita Boyers as a guide. select trip_name from trip where type='Biking' intersect select trip_name from trip,guide where trip.state =guide.state and guide_num='BR01'; recieved error in query expression . (Error 3075) what happened needs to work microsoft access

Find the guide last name and guide first name of all guides who can lead a paddling trip. ( Note : The query results should include duplicate values. select guide.last_name,guide.first_name from guide,trip where guide.state=trip.state and type='Paddling' order by last_name,first_name; but still had duplicates need to remove the duplicates

Make a new table from the existing HR_EMPLOYEES table, using CTAS: make table a2_employees as select * from hr_employees; Make a PL/SQL procedure to display some information from the newly created table A2_EMPLOYEES and also to update salary, for a specific department ID. The procedure should read row by row using explicit cursor and calculate

Create the database related to the topics from the enclosed list Your database should have : a) minimum of 4 tables; b) Each table should have a minimum of 5 records and 5 fields in each record. Most of your records should have a different type of fields (currency, number, text, calendar, etc.); c) Minimum of 2 queries d) Describe your generated

Discuss why cyber-security is critical to the future of Emergency Management? What challenges do you see in the Emergency Management field related to cyber-security.?

Create 5 (or more) tables that correspond to the entities in your ERD. Choose tables that are in relationship with each other so you can use the tables for the following queries. Show the SQL statements. (Do not forget the primary and foreign keys, and any appropriate constraints.),Insert some data into each of the tables (at least 10 rows per

Using the Adventure Works database, select all the rows from the table Product in Production schema and sort it by product name within standard cost with standard cost going from highest to lowest.

Design and implement a web application with database support. MySQL server is the recommended DBMS. However, students can create server-side applications in PHP, C#, JSP/JDBC, ColdFusion, Node.js, or your choice. The final deliverable needs to be deployed to the Internet, hosted by Webhost or by the servers of your choice. Also, in the submission

How does this SQL question work? Can someone give me an example and explanation?

Write and Run SQL Statements in MS Access MySQL focusing on INSERT, UPDATE, DELETE, LIKE, Wildcards, IN and BETWEEN. If you are using MS Access, you will need Ch07LargeCo.mdb attached to this assignment, if you are using MySQL, you will need to run Ch07LargeCoMySQL.txt to create the table. For every question, copy paste your SQL and the result

Write a Query to provide all the customers who have a strike for Violating Max Capacity Write a query to pull the employee records for the employee(s) that have the highest number of maintenance jobs completed Write a query to pull the facility who had the most recent maintenance completed. Then, build upon that query to find out who was the last

Show titles, genre, director last name, rating for all movies with rating average critic rating of all movies. Use a subquery in the Where clause to find the average rating within the subquery. Order by title. (SCREENSHOT) Aliens Aliens Aliens Aliens Aliens Aliens Amadeus Amadeus Amadeus Amadeus Amadeus American Beauty American Beauty American

Develop a database to help "YourPerfectEvent" event planners to manage their operations. The database must be able to accommodate the following. A customer contacts the company and makes arrangements for their event. They are required to put down a 10% deposit on the event and then to make payments as scheduled in their contract. The database

Use the bookstore database in order to run some reports for upper management. The SQL file for this is already in your Playground, called gravity_books.sql. Upper management has some questions they need answered, but are unsure what exact data/fields will help them best answer the questions. You'll need to think about what their actual problem is

Use the ERD below and write SQL statements to find: 1- TotalAmount for Year 2004 and ProductName = "1934 Ford V8 Coupe" 2- TotalAmount for State "MA" 3- TotalAmount for Country Australia and Territory APAC 4- Total Amount for Year 2005 and Product Line "Ships" 5- TotalAmount for City "Glendale" 6- TotalAmount for Year 2004 and Product Line

1. List the first name and surname of the residents who reside at 887 Vine Rd. 2. List the office number, address, and monthly rent for properties whose owners live in Washington State and own a two-bedroom property. 3. Find the service ID and property ID for each service request whose estimated hours is greater than the number of estimated hours

1. Using Dia, draw the ER diagrams to represent the relationship type suggested by each of the following scenarios. You do not need to include any attributes in your ER diagrams, only the entity type and relationship types. 1. There are two entity types nurses and patients. Each nurse treats many patients and each patient is treated by many

An Accountable Care Organization (ACO) has engaged us to assist in helping them develop a predictive modeling solution to reduce the prevalence of strokes in the population they manage. Their plan is to develop an outreach program where a team of health coaches will work with members to reduce their risk of stroke. However, they do not have enough

Determine the highest gross pay by the employee in the table db1. costco.employee. Use the tables db1. costco.job, db1. costco.hours db1. costco.employee. Format your column with a column heading and format the data returned by your query with a leading dollar sign (), comas every three digits and two decimal places Determine the lowest weekly

Answerer the following question with he reference of diagram given below SQL query to return the 10 most recently shipped orders with freight costs less than 50. Remember to filter out null ShippedDates by using the IS NOT NULL keywords. (Hint: OrderID include 11067, 11069, etc.) SQL query that shows the total number of orders for each customer in

categories 123 categoryid ADC categoryname ADC description picture order details 123 orderid 123 productid 123 unit price 123 quantity 123 discount E customers noc customerid ADC company name ADC contact name ADC contact title ADC address ADC City ac region ADC postal code noc country noc phone noc fax products 123 productid noc product name 123

Make a SQL from below and the descriptions for each SQL using the tables given below 1. Show Referential Integrity has been establish. (Eg. Try deleting something that is already ordered) 2. Join several tables together. 3. Show several layers of Nested Query. 4. Make a View (Eg. with new heading). 5. An SQL with several compounded conditions. 6.

Tablesinyngah CUSTOMER EMPLOYEE ITEM ORDERLINE ORDERS 5 rows in set (0.000 sec) MariaDB yngah SELECT FROM CUSTOMER CUSTNUM CUSTFNAME CUSTLNAME PHONE 91810 LEE 93010 BILL 94410 FULTON 96410 I AL 99710 JAMES CARL RAVEN BROWN SMITH JAKE 5 rows in set (0.000 sec) 51010 BRYCE DER I 52020 MACY DER I 53030 DANNA DER I 54040

Based on my ERD given below answere the following question 1. Drop existing tables and sequences first. 2. Create/Alter Statements for ALL Tables and Constraints. Include Primary Key, Foreign keys, and unique keys specified. 3. Create Indexes for Natural, Foreign Keym and Frequently Queried Columns. 4. Two Sequences, ideally for each PK column. 5.

PATIENT MRN LNAME FNAME DOB ADDRESS OCAN HAVE.. CAN HAVE PROVIDER PROV NUM NAME FNAME DOB SSN CAN PRESCRIBECANBEPRECRIBED PICKUP PICKUP NU MAN (FK) MED RX NUIFK REF DATE PICKUP AMT HHH MEDICATION MED RX NU MEDOTY REF AMT PROVNUM(FK) PICKUPNU(FK) CAN HAVE CAN BE ACCESSEDCANACCESS PHARMACIST PHARM D LNAME F NAME DOB ACT UC

Create a schema with the name of SpeedyCo. Copy and paste the provided script (Lab6OnyxcoMySQL.txt). This will create all the required tables and data. Speedy company is a software development company. You are hired as a database designer and SQL developer. You have already designed the database for the company and created them. Now you are asked

The problems use the Customer, Order Tbi, and Employee tables of the simplified Order Entry database. Chapters 4 and 10 extend the database to increase its usefulness. The Customer table contains clients who have placed orders. The OrderTbl contains basic facts about customer orders. The Employee table contains facts about employees who take

What are the fields that are used If you want to store all your customers in a relational database might you want to store for each customer and explain in detail which of those fields belongs in a separate, second table. Also explain how you would normalize the data and touch open the primary and secondary key

Using a numeric loop that runs from the first student id = 105 to the last student id=150, select student first_name, last_name and also how many sections the student is enrolled into. Maximum number of sections a student is allowed to enroll into is 5. If student enrolled in less then 5 (use here your IF statement), then calculate how many more