Databases Questions and Answers August 18, 2021

Determine the decimal value stored in register (Data memory location) Ox92 after execution of the following assembly code. MOVLW .24 CLRF INCF GOTO MOVFF Done MOVLW ADDWF BZ Next BRA 0x92 Ox92, 1 Done 0x90, 0x92 .85 Ox92, 1 Done Next

write a stored procedure in Microsoft SQL Server that deletes an order given bt its ID, if the status of the order is "new" . If the order does not exist or the status is not "new", then throw an error. You only need to delete the order record; no need to take care of any items belonging to the order. You need to protect the execution of the

Order ID Date Deadline PlaceOfBusinessID StatusID Payment MethodID Status 7 ID Name

Write and submit a create stored procedure statement for a stored procedure named EmpOrderCount in the dbo schema that takes a territoryid as a parameter. The parameter must be named TerritoryID. The parameter should default to null. The stored proc should not return the rowcount. The stored proc should raise a SQL Server error with a severity of

Using the Baseball Sample database from here https://www.sqlskills.com/sql-server-resources/sql-server-demos/ Code an INSERT INTO statement with proper data values that would successfully add a record to the table. INSERT INTO players (lahmanID, playerID, managerID, hofID, nameFirst, nameLast, weight, height, bats, college). Paste your statement

Write a code block containing part of the definition and execution which realizing the following function: according to the department number output the names, salaries of all employees in this department.

Consider the following (unoptimized) MIPS assembler program 2 3 4 5 6 6 7 BA 8 0 9 10 10 .text .globl main int procB (int a, int b) int 0 c a b return C 44 11 procB 12 addi sp, sp, 8 13 SW 3ra, 4(sp) 14 15 add 16 SW 17 1w 18. 1w SW zero, 0(sp) t0, a0, al t0, 0(sp) v0, 0(sp) ra, 4(sp) sp, sp, 12 37 addi 38 jr Sra 39 m 40 int main() 41

Write a Stored Procedure SPReportNEWBudget using the view you created in Part 13). Use CURSOR to write the stored Procedure for the tasks below. does the following tasks Your Stored Procedure SPReportNEWBudget 1) It creates a new table NEWDeptBudget as below NEWDeptBudget has 5 columns Dept No (Int) Dept Name (Char(30)) COUNTEmp (INT) NewSUMSalary

1. Write a stored procedure to find the earliest registration date by a OUT parameter. (use function min() 2. Write a st stored procedure to find the registration date by a IN user_id parameter and a OUT parameter 3. Write a stored procedure to add a new user. Provide IN parameters of user_id, first_name, last_name, email, pass and

create a stored procedure using Sybase or Microsoft that will delete an employee from a sample "emp" table. This table probably exists in your database already but if it does not, there is code to create it in the HW directory. Make sure you use proper transaction control and error checking as described in class. 1. Log into your Sybase or

2 12 use db25 go 1 select from emp 2 go empid empname Jones Smith 3 Potter 4 Clinton (4 rows affected) 1 salary deptcode 00 SALE .00 SALE 00 TECH .00 MNGT

What is difference between Joins and Subqueries What are the different type of subqueries? What benefit can get by a company by using Business Analytics. What is the store procedure ad what is the purpose of it? what is execution plan can we change the execution plan? Advantages and Disadvantages of Stored Procedure? What is subquery and what are

Insert into employee (fname,lname,ssn,bdate,addres,gender,salary,supperssn,depnum) VALUES('Reem' ,'Khalid' ,9,'1984-08-13' ,'Dammam','F',12312,4,9) . why it gives: Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`company`.`employee`, CONSTRAINT `depnum` FOREIGN KEY (`depnum`) REFERENCES `departmen` (`dnumber`))

After enforcing foreign key constraint using on delete cascade rule, is it possible to delete a record in the child relation whose foreign key value is NOT NULL before deleting the corresponding primary key record in the parent relation? please explain in detail.

design and present a DBS to track students attending classes a CSC class that has a maximum of 21 students and meets 32 times a term. I need to be able to generate 3 reports as follows: 1. A roster of all students attending class on a given date. 2. A list of all class dates a given student attended. 3. A Report that tells me how many students

Create Database AIRPORTOPS Create Table Airport (Acode Char (3), Aname Char (20), Constraint APK Primary Key (Acode)) Create Table Flight (Fnumber Char (6), Airline Char (20), DAirport Char (3), AAirport Char (3), Constraint FPK Primary Key (Fnumber), Constraint DFK Foreign Key (DAirport) References Airport (Acode), Constraint AFK Foreign Key

Each case study should have a minimum of 800 words double-spaced, Times New Roman font type and 12pt font size. (With that in mind, 800 typed words is about three pages, not including the title and reference pages). Case studies must be formatted according to APA guidelines using a MS Word document and include at least three (3) references that

1- Use query tree to optimize the following query. Use the tables that was provided in previous assignment SELECT cust_num, cust rep, name, rep. office, city FROM Customers, Salesreps, Offices WHERE empl num=cust rep, and rep. office = office AND credit limit >= 5000 And region="western And office in (11, 12) 3- Use query tree to optimize the

Salesreps Table EMPLNUM NAME 105 Bill Adams 109 Mary Jones 102 Sue Smith 106 Sam Clark 104 Bob Smith 101 Dan Roberts 110 Tom Synder 108 Larry Fitch 103 Paul Cruz 107 Nacy Angelli Offices Table OFFICE CITY 22 Denver 11 New York 12 Chicago 13 Atlanta 21 Los Angeles Customers Table CUSTNUM COMPANY 2111 2102 2103 2123 2107 2115 2101 2112 2121 2114

Write one or more SQL statements to create appropriate index(es) to support optimization of the query described below. Show the title of all posts created in specific month Write one or more SQL statements to create appropriate index(es) to support optimization of the query described below. Show the name of each user, along with the number of

Use the relational database schema below to answer the questions in this quiz. RELATIONAL (Module 2) VOTE userID (FK) postiD (FK) type (boolean updown vote) USER VOTES on POST POST postiD userID (FK) title contents createdate updatedate USER writes POST USER userID HHname email bio social createdate H COMMENT useriD (FK) postID (FK) contents

In what situation can we use the following rule when optimizing queries Please also explain why we use this rule. Oc( RS ) (0 c1 (R))(0 c2 (S)) Consider the relations STUDENT(SNAME, SID, BDATE, ADDRESS, DNUM) COURSE(CNAME, CID, LEVEL, LECTURER) COURSE TAKING(STUDENTID, COURSEID, GRADE) as well as the following SQL query SELECT SNAME, CNAME, GRADE

What are optimizer hints? Give an example of their use. What factors should you take into consideration when creating and using an index? What does the database do differently between optimizing DDL vs DML statements? List and describe 5 things that could cause a delay in query processing.

Consider the following Movie database for Question 12 actor actid actfname actIname actgender director dirid dirfname dirIname moviedirection dirid movid integer char (20) char (20) char (1) integer char (20) char (20) integer integer 18 18 moviecast actid movid role movie integer integer char (30) movid movtitle movyear integer char (50) integer

State the 3 stages of queries written in SQL are processed by Relational Database Management Systems and explain your own words and with detail the purpose of each. Using the generic relational algebra expression translated directly from a typical SQL statement: Argue and explain in detail why query optimization is needed especially when the

Briefly explain What a distributed database is. Outline the advantages and disadvantages of distributed databases. Explain the following: Distribution transparency Transaction transparency Performance transparency Query optimization

Apply transformations on the given database schema. R (a, b, c, d) to optimize the following queries based II (0gx (Ge 15 (RMSMT))) S (d, e, f) T (f, g)

Based on MySQL internals, which query is optimized. Be sure to explain your results. a. select from largeco.lecustomer select from largeco.lgcustomer where cust Iname like HUD where cust Iname like HUDSON b.

A database system has been implemented using Oracle. The list below shows the relations used in this system: Country (Name, Code, Capital, Area, Population) Organization (OrgName, Abbreviation, Established) IsMember(Abbreviation, Code, Joined) Write an SQL query to show the country name and Organization name for countries who have joined between

Review six queries that require optimization . 1. SELECT Order.CustomerID, Product.ProductID, OrderDetail.OrderID FROM Product, OrderDetail, Order WHERE Order.OrderID = OrderDetail.OrderID AND OrderDetail.ProductID = Product.ProductID AND OrderDetail.Price = 50 a. Optimize query performance using appropriate strategies (that is, optimizer hints,

What is the difference between SQL performance tuning and database performance tuning? What are the three phases the DBMS processes a query in? What do you do in each phase? Why is an index on every column of a table inappropriate? Where do database statistics come from? Explain what RAID stands for, and describe the levels.

Explain how the Cost Estimation and Heuristic Rules can be used to optimize query processing speed? A distributed database system is a collection of logically related databases that co-operate in a transparent manner. Discuss the benefits of a distributed database over a centralized database.

There are four approaches to optimizing database performance: indexing, denormalization, view materialization and query optimization. Explain a) how each of the approaches can be used to improve the performance of a database and b) what are the differences among these four approaches.

When the query optimization process is complete, what is selected to run the query? What is the ability of a database to allow multiple users to affect multiple transactions ? What is a part of a DBMS that determines the best execution of SQL queries?

What are the costs associated with the execution of a database request in a distributed database. Describe three of them Describe three examples where a graph database will be more suitable than a relational database

1. Explain the phases and functions of the query optimizer. 2. Identify the tools to edit query optimizer strategies. 3. Explain how performance of a database system is measured. 4. Differentiate between response time and throughput.

Consider the following SQL Query: SELECT FNAME, DNAME FROM EMP ASN E, DEPT AS D WHERE E.DNO = D.DNUMBER AND D.DNAME =CCI AND SALARY > 15000; Draw the initial query tree for the above SQL query, and then show how the query tree is optimized by applying the Heuristic Rules.

Consider a database with the following schema CUSTOMERS(id, name, address, city, state, zipcode) ORDERS(id, date, custid, salestax, total, status) The SQL query is SELECT SUM(total) FROM customers, orders WHERE customers.id orders.custid AND state OH AND city Columbus AND date 20230310 AND status shipped Draw an unoptimized query plan that will

1-Write the SQL queries for the following items, considering the SQL Query Optimization Techniques studied in classes. (a)List all the actors who have not acted in any movie between 2015 and 2020. (b)Find how many movies are there in each genre. (c)Consider the following query specified on Movie database: SELECT A.act_lname FROM actor A,

Consider the schema for a flight schedule database that keeps track of carriers ( such as British Air), Airports (such as JFK Airport), and flights schedule (such as Flight 124 from Atlanta to London and its carrier is British Air). Airport (AID, AName, ACity,ACountry) Carrier(CID, CName, CCity, CCountry) Flight(FromAirport, ToAirport, Date, CID)

THE task is to optimize this query and draw the best possible query tree for this query. Take appropriate database statistics to support your answer, if needed.

Consider the following part of database, and the SQLRA query Student (RNO, FirstName, LastName, BirthDate, Gender, CGPA, BatchID, CampusID, DegreeID) Course (CID, Title, CreditHours, Course Level, Course Type, OfferingDept) Grade (Rollno, Course ID, Letter Grade, GPA, Semester, Year, LastUpdate) SELECT S.RNO, S.FirstName, G.LetterGrade FROM

what is Relational Database Constraints. how did you specify these constraints? Relational Algebra Differences between relational algebra operations and relational languages SQL?

How efficient a query is can have a tremendous effect on how the database responds to our request for data. Discuss some of the things that go into effective query optimization and how those processes have an effect on a query.

Explain the 3 layers of a three-tier application for the example of a web application with database access. State into which layer the following terms belong: web server database server web browser php code transaction processing cgi scripts html form query optimization and execution

How MySQL, PostgreSQL, IBM DB2 database management systems apply query optimization on the user queries?

Descriptions and explanations of Indexes and the database architecture of your design. What indexes are you going to be using in which tables, and why? Query optimization in your design. There are going to be many queries. Do you need to optimize them? If so, why and how? If not, why not?

Define the following database concepts and explain the use/benefits. Equivalent query Query cost Access paths Conjunctive selection Index, nested loop join Using statistics about data tables to optimize queries. Random sample/sampling

Consider the database described by the following schema: Hospital(hospital_code, name, address, phone#, num-of-beds) Patient(registration-num, name, address, birthdate, sex) Occupany(registion-num,hospital-code,ward-code, bed-num) Draw an initial query tree and show how an initial query tree is optimized by an algorithm.

Describe the different types of database requests and transactions. What is the objective of query optimization functions? What are the different types of query optimization algorithms? Describe the three data fragmentation strategies. Give some examples of each. What is data replication, and what are the three replication strategies? Explain the

For each of the following queries perform heuristic query optimization using query trees. Assume the Employee database as the schema used in the queries SELECT E.Fname, E.LName, E.Address FROM EMPLOYEE E, DEPARTMENT D WHERE D.DName=``Research'' AND D.Dnumber=E.Dno; Select Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum=Dnumber AND

What a database lock is? What the query optimizer is and how it works to prevent locks? What multiple granularities is and how it is related to locks.

1-Explain the following terms and give examples I. Weak Entity II. Unary III. Binary IV. Teruary V. Domain VI. Entiry set or type VII. Entity 2- Differentiate between natural join, Uion Join and Outer Join. 3-What are subqueries? What are the types of subqueries? 4- Explain Query Optimization 5-List and explain the techniques of optimizing queries

List the steps taken by a query optimizer to optimize the below SQL query using heuristics for query optimization (heuristic rules). SELECT P.Fname, P.Lname, D.Dname FROM Patients P, Doctors D WHERE P.Ddoctor D.Dname AND d.Dgender M AND p.Bdate 19601231 For the toolbar, press ALTF10 (PC) or ALTFNF10 (Mac).

Following picture is the query form # 2

Show the directors name, movie title and movie release country for all movies whose release country is NOT the USA. Use the query from 2 as a subquery to select all movies with a release country not in the USA. dirfirstName John Danny Danny James James James Michael Jack Frank Milos Mel Alfred Richard Stanley dirlastName Boorman Boyle Boyle

Show the reviewer and the directors name for the movies with the highest review ratings in the 1990s order by the reviewer. revname Fernando Sharp Flagrant Baronessa Scott LeBrun dirfirstna... dirlastname Darabont Van Sant Singer Frank Gus Bryan,director id INT dirfirstName VAROHAR(30) dirlastName VARCHAR(30) Indenes movie id INT movitle

1. Write the SQL query to retrieve the following information of all employees: a. Employee number b. Employeelastname c. Date of hire d. Jobcode. The company wants to send a check to their employees as a gift. The amount of the check will be calculated based on the employee's experience. So, for each year of working in the company, they will be

0 0 1 CREATE the following tables a. Dept triggers Add Identity Column (1000,1) b. Emp triggersAdd Identity Column (1000,1) c. Emphistory dbo.emptriggers Columns empid (PK, int, not null) empname (varchar(50), null) deptid (int, null) Keys dbo.emphistory Columns empid (int, null) deptid (int, null) isactive (int, null) Keys Constraints TRIGGERS

How many value type objects are there in the ORM diagram? How many ternary facts are there in the ORM diagram? How many derived facts are there in the ORM diagram? How many external constraints are there in the ORM diagram? How many inclusive-or constraints are there in the ORM diagram? How many mandatory constraints are there on the ternary facts?

TourDescription Fare (AUD) Duration (h) is of has has child has adult serves a snack serves a main meal Date (ymd) Gender (.code) has DriverName was born on For each TourShift, has Tour (.nr) has Ais of has Stop Nr at... TourShift (.code) Driver (.nr) visits ... departs at e return Time departure Time tour.duration. Site (.code) is fluent

Make an SQL queries for displaying the following information. Please remember each query requires using only one table. No JOINs are required. 1. Provide the number of Vendors hailing from each state. Provide suitable alias for the vendor count. This query will display one row for each Vendor area code in the descending order of Vendor count. 2.

REGION PK REGIONID REGIONNAME CUSTOMER PK CUS CODE CUSLNAME CUSFNAME CUSINITIAL CUSAREACODE CUSBALANCE VENDOR PK VCODE VNAME VCONTACT VAREACODE VPHONE VSTATE VORDER Hhas OPK Hgenerates HOsupplies STORE STOREID STOREZIPCODE FK1 REGIONID GENERATES INVOICE PK INVNUMBER FK1 CUSCODE FK3 EMPNUM INVDATE FK2 STOREID PRODUCT PK PCODE PDESCRIPT PINDATE

What could be the possible values for all the attributes?

CREDITCARD Column AttributeColumn Name Num. 1. Credit CardNumber 2. Credit CardOwner Name 3. 4. Merchant Code 5. ExpDate Credit CardIssuing Company 6. AddressLine1 7. AddressLine2 8. 9. 10. Zipcode 11. Country Credit Card Limit Credit CardBalance City StateCode 12. 13. 14. Activation Status Generic Oracle Data Type Name Data Type Name Is it

What is dirty data in a database? Provides at least two examples. how a dirty data could exist? How can you avoid the existence or access to dirty data?

Create a table Moneyxxxx with the following fields and constraints. mid int primary key autoincrement, code varchar(50) unique, cid int, type one char, amount float, mydatetime datetime, note varchar(255) All fields cannot have NULL values, except note. mid is the primary key, and the value should be auto increment by the DBMS. cid is a foreign

Create a temporary table for each of the data components, as described in the table below. Temporary Table Name Topic 4 Assignment Query #MyProduct Find the product ID for the LL Road Frame - Black 60 #MyPriceHistory Find the listing price of the LL Road Frame - Black 60. Display ProductID, EndDate, and ListPrice. #MySalesOrderDetail How many

Enter the statement to display the number of items and the average cost of all items in the Items table. The aggregate function to get the total number of items will not use a specific field in the table. Use a column alias of Number of Items for the number of records returned. Use a column alias of Average Cost for the unit price. 4 Example

Enter the statement to display these columns in the invoicelineitems table 1. The line item description 2. The count of those line items (will not use a specific field) 3. The average price of the line items . Do not use aliases You will have two summary functions .Watch your spacing Example output Freight Intl shipment Telephone service Cover

Write a nested function that changes the output from the last query to have a rounded average price of line items. Round to 2 decimal places. Example Output Freight Intl shipment Telephone service Cover design 51 3 1 3 526.19 181.23 95.00 901.03

Create a stored procedure called "sp_TransferAccounts" The stored procedure will take three IN parameters: Source account number Destination account number Transfer amount The stored procedure will take on OUT parameter called 'transaction_status' The stored procedure will contain a transaction with rollback. The transaction must include the

The system is required to store information about movies for a Movie Hire Business. The users wish to keep track of the following data: Members have a username (no two members are allowed to have the same username), password and address Every movie has a unique movie id, title, year released and category. A movie only has one director but a

Find the service ID and property ID for each service request whose estimated hours are greater than the number of estimated hours of at least one service request on which the category number is 5. List the address, square footage, owner number, service ID, number of estimated hours, and number of spent hours for each service request on which the

There are numerous database vendors in the world each with their own brand of SQL. There is also a SQL standard governed by the American National Standards Institute (ANSI). Most large database vendors practice some form of ANSI-standard SQL but will also have their own unique SQL commands. Join your fellow students in a discussion on SQL

Let R(A,B,C,D) be a relation schema with the following set of FD's: F = {AB C, C D, D A}. (a). Find all the keys of R. (b). Find all the super keys of R that are not keys.

Make a mailing list of vendors who provide bikes. Include the vendor's name, address, city, state and ZIP as well as a Type column that says "Vendor." Do not rename any columns other than the Type column. Make sure each distinct vendor only appears once. Sort the list by vendor name.

1.Write the query below to show the movie title that has the longest runtime in each year from 2000 to 2010 select to_char (movie_release_date, 'yyyy') as year , movie_name, movie_runtime from cinema.movie where extract ( year from movie_release_date) between 2000 and 2010 and ( extract ( year from movie_release_date), movie_runtime) in ( select

REVIEW Preview id reviewscore De reviewdate F movieid CUSTOMER custid custname custaddress . custsuburb custpostcode . custstate . custcontact number custgender custdob HI Psaleid F F SALE saledate custid movieid salenumberoftickets saleunitprice saletotalprice cinemaid modeid staffno STAFF P staffno staff name IH staffgender P MOVIE

create a query to write list of all actors who played in a movie called "AMADEUS HOLY". SELECT f.title AS FilmTitle, CONCAT(last_name, ', ', first_name) AS ActorName FROM film f JOIN film_actor fa ON f.film_id = fa.film_id JOIN actor a ON fa.actor_id = a.actor_id WHERE title = 'AMADEUS HOLY';

write a query that produces a list of documentary movies (where the movie category is 'Documentary') and a movie description containing the word 'Drama'. You will need a triple join between category, film_category, and category tables. SELECT c.Name AS CategoryName, f.title AS FilmTitle, f.description AS FilmDescription, f.release_year AS

Retrieve the last names of all employees who work in the department that has the employee with the highest salary among all employees. You can run your query in MariaDB if you have populated the tables correctly. SELECT ____________ FROM EMPLOYEE WHERE DNO = ( SELECT _____________ FROM EMPLOYEE WHERE SALARY = ( SELECT

WORKS ON Essn 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 Pno Hours 1 32.5 7.5 40.0 20.0 20.0 10.0 10.0 10.0 10.0 30 30.0 10 10.0 10 35.0 5.0 2 3 1 2 2 3 10 20 30 30 20.0 15.0 NULL 20 20 PROJECT ProductX ProductY ProductZ Pname

Define UNION and give one (1) example in ORACLE SQL commands. Define UNION ALL and give one (1) example in ORACLE SQL commands. Define INTERSECT and give one (1) example in ORACLE SQL commands. Define MINUS and give one (1) example in ORACLE SQL commands.

In python Create a class called Person. From this class, inherit another class called student. Assume suitable data members and member functions for these two classes. In both the classes, define the __str__ function and demonstrate method overriding

If lock scope is a single row, and T takes an exclusive lock on CountryID 578, what happens to T Pick 000 if lock scope is a block, and T takes an exclusive lock on CountryID 578, select the row(s) that are locked. Norway Europe Guyana SAmerica Asia Bangladesh 000 578 328 50 T SELECT CountryID PROM Country WHERE CountryID578 798 Tuvalu 616 Poland

Nowak Gravel Company received a contract to supply gravel for three new road projects located in the towns A, B, and C. Construction engineers have estimated the amount of gravel which will be needed at three road construction projects (tab. 1). Nowak Gravel Company has three gravel plants located in the towns of X, Y, and Z. The gravel required

How many regions are in the continent of Antarctica? Display this number as "Number of Regions". Display the name of the continent as 'Continent'. Also display the grand total surface area as "Total Surface Area". Insert your snip of the query and tesgltset together here: Which countries have more than 1D ofcial or unofcial languages listed?

With wearable technology, you can track your entire life. Nike's Fuel bandand Jawbone's Up track all of your physical activity, calories burn, and sleep pattern. You can track your driving patterns, tooth-brushing habits, and even laundry status. The question now becomes how to track all of your trackers. A new company called Exist incorporates

1-create the three tables with their SQL queries and paste the code in text format below. Make sure while creating the tables: Must have primary keys in all the tables. The attributes which can potentially be foreign keys must have NOT NULL Constraint. Gender attribute in users table should CHECK for 'M' or 'F' 2- Can you please write 2

id 2 3 4 5 City name Halifax Calgary Boston New York Toronto

Label v as active Every vertex is initially unexplored for each outgoing edge, e, that is incident to v in G do if e is unexplored then Let w be the destination vertex for e if w is unexplored and not active then Label e as a discovery edge DirectedDFS (G, w) else if w is active then Label e as a back edge else Label e as a forwardcross edge

department: employee: increment: Use the created table as above, solve the problems as mentioned below. You will have to import the respective CSV files of the above created tables as without them, it is impossible to solve the questions below. If you are not able to upload the files successfully, do not leave the query questions. Just write the

deptno dname loc 10 ACCOUNT NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIO BOSTON

Provide the SQL StatementsCode and the screenshot of the output (see example below) for the following problems from Chapter 7 1. Modified 45 Write a query to display the least average product price of any brand 2. Modified 23 Write a query to display the least customer balance of any customers that are listed in the customer table 3. 99 4. 49 5.

Create a function by the name teamDetails(mgrID int). This Function will return the table with empno, ename, manager name of the employees, when the input is a specific mgrID (this is the input parameter of the Function).

Please Write SELECT INTO statements to create one test table named Vendor-Invoice that contain the results of a natural join between the Vendors and Invoices tables; Please Write an INSERT statement that adds a row to the Invoices table with the following values: VendorID: 132 InvoiceTotal: $434.58 TermsID: 2 InvoiceNumber: AX-014-027

1-Customers can use ATM machines to deposit cash. ATMs are located in various locations, such as inside supermarket, convenience store, bank branch. Some are located inside standalone facility that requires swiping cards to enter. Customers and related information are identified from their debit cards. A customer may have more than one account

Data from one or more tables- use zag -- Marketing needs a list of customers and what product they have purchased -- which also shows category name and vendor name for that product. -- Generate query that returns customername, productname, categoryname, vendorname -- order the list by customer name -- Your list should contain 9 items

category categoryid CHAR(2) categoryname VARCHAR(25) Indexes product productid CHAR(3) productname VARCHAR(25) productprice DECIMAL (7,2) vendorid CHAR(2) categoryid CHAR(2) Indexes 1 T 1 vendor vendorid CHAR(2) vendorname VARCHAR (25) Indexes includes productid CHAR(3) tid VARCHAR(8) quantity INT Indexes HH salestransaction tid VARCHAR(8)

Write SQL queries and snapshot of each query result as shwon above You can do only queries as well 1. Retrieve the product ID, product name, category ID, and product price for each product in the FW product category, sorted by product price in descending order. 2. For each product, retrieve the ProductID value and the total number of product items

REGION RegionID RegionName STORE StoreID StoreZip RegionID (FK) PRODUCT ProductID ProductName ProductPrice VendorID (FK) CategoryID (FK) SOLDVIA ProductID (FK) TID (FK) NoOfItems SALESTRANSACTION TID TDate StoreID (FK) CustomerID (FK) VENDOR VendorID VendorName CATEGORY CategoryID CategoryName CUSTOMER CustomerID CustomerName CustomerZip

For the following User View, determine the UNF and the 1NF and hand in this page to your instructor. Order Order Date Number 12489 20160902 12491 20160902 12500 20160905 12504 20160905 UNF Premiere Corporation Order Detail Report 12494 20160904 315 12495 20160904 256 12498 20160905 522 1NF Cust number 124 311 124 522 Part Number AX12 BT04 BZ66

Find all capital cities that are one of the three largest cities in the state/province using intersect Tables are set up like this Government Table -State -Capital -Governor -LowerHouse -UpperHouse -Country Large Table -State -Firstcity -SecondCity -ThirdCity -Country Ticket to Ride Table -State -City -Tickets -Country This is my query so far I

Write a PowerPoint slide presentation about this ERD diagram . Project name is "Small community College" .

Student id Grade d Course Grade Recartment MReceive Name Mais d Name Department 1 Dept Type Major d Student id (Major credit Name Student Has Major Address elongs Has Department id Employee id Enrollment i Employee id Enroll Name Employee Semester id Velor Detal Address) Date instructor fag Enrollment Courseid Student id Has Semester Time Major id

Create the following SQL queries using the specified DML statement in the query window. Comment each step in the script. A. Write a SELECT statement that returns these columns: A. The DepartmentName column from the Departments table The CourseNumber column from the Courses table The FirstName column from the Instructors table - (If null, display

List foreign key constraints that this database should have. Your answer should indicate the referencing table (the one in whose table definition the constraint should be included), the referenced table, and the attribute(s). You may use a format like the following without writing out the whole table definition or keywords FOREIGN KEY ... Order.

Customer (phone, bldg Num, street, apt Num) Dish(item, description, category) Dish Size(item, size, price) Order (order Num, phone, order Date, status) Order Items (order Num, item, size, qty)

Design a database for an equipment rental business called MyRentals. MyRentals rents equipment in five categories: construction, ladders, lawn care, paint and plumbing. MyRentals has been storing its data in some spreadsheets that track customer rentals and payments received, equipment. However, business is booming and the spreadsheets are

What are the top ten worst rides with respect to time and distance? In other words, who sat in the taxi the longest and went the shortest distance? You should remove any ride whose trip_distance is 0. We want to maximize the following calculation: (number of seconds in the taxi / trip_distance) To ensure you are picking the correct row, your

Specify the relationships between the table in your revised design. It's how you justify the decisions you made that matters here. Plus, there may also be an occasion where one of your peers is simply wrong. It will be up to you to explain why. Did your revised design differ from your classmates? If so, how? Why? Who is right? Are you both right?

1. Insert three documents in your collection that includes an array value of three or four elements for each new document. For two of those documents have different array elements. With the third document use the same array elements as your second document, but this time have them ordered differently. Also include a key value pair that includes a

Write a query that will be used for invoices. The query needs to show Order Line level information, and then the final row will show the Order Totals. To earn the extra credit, you must use the UNION set operator. The OrderLine level columns should include OrderID, OrderDate, ProductName, Price, Quantity, and LineTotal. The Order Total level

PK PK Employees EmployeelD FirstName MiddleName LastName StreetAddress City State ZipCode Title Salary SMALLINT VARCHAR(25) VARCHAR(25) VARCHAR(25) VARCHAR(35) VARCHAR(25) CHAR(2) VARCHAR(10) VARCHAR(25) DECIMAL (8,2) Have Dependents FirstName LastName BirthDate SSN EmployeelD SMALLINT DependentNumber TINYINT VARCHAR(25) VARCHAR(25) DATE CHAR(11)

In python Create a class called Date. It should contain data members day, month and year. It should also contain functions to set and display date, as well as function to initialize date objects. Overload the == operator to check if two dates are equal

Start Microsoft Excel. You should be looking at a blank worksheet. This exercise will help you compute your grade point average for the courses you are taking. In cell A1, type Grade Point Average Calculator in bold font. Starting in cell A3, type the course identification. This varies from school to school but usually starts with a departmental

Produce an E-R diagram and a relational model of your design. Harry Happy is a used car dealer and runs one of the most profitable car yards in Melbourne. Harry has asked you to design a database for his office computer so he can record details of his sales. Harry has given you a copy of his current paper invoice to use as a guide: INVOICE HARRY'S

1. Write a PIVOT query that can place the countries in the rows (down the page) and years in columns across the page 2. Copy your SQL code into the word processing document (into an appendix) 3. Screenshot the dataset produced by the PIVOT query and also copy that image into the appendix. Verify that the numbers match the matrix report created in