Expert answer:Need help with an SQL project

Answer & Explanation:I need help with this project for my database class, my proposal is attached, please take a look at it.Proposal.docx This is an advance database class, so I’m attaching some of our labs to let you know what type of queries we are dealing with:Lab6StoredProcedures.docx Lab5AdvancedSQL.docx Lab7TransactionsandLocks.docx Lab8QueryAnalysisandIndexing.docx Thanks!
proposal.docx

lab5advancedsql.docx

lab7transactionsandlocks.docx

lab8queryanalysisandindexing.docx

lab6storedprocedures.docx

Unformatted Attachment Preview

For our term project, we intend on using a baseball archive database to conduct our
own benchmark testing. The database contains complete batting and pitching statistics from
the year 2014, plus fielding statistics, standings, team stats, managerial records, post-season
data, and more. It is copyrighted by its creator Sean Lahman and licensed under a Creative
Commons Attribution-ShareAlike 3.0 Unported License, meaning we have permission to share
and adapt the material.
For the benchmark testing, we will develop queries similar to the SQL statements that
we use in class to run against Lahman’s Baseball Database in four different database
management systems. We will install and set up the following database management systems
to test: SQLServer, MySQL, PostgreSQL, and Ingres Actian Vector Express.
SQLServer is a proprietary relational database management system developed by
Microsoft. There are different versions of SQLServer that are aimed at different audiences for
several uses. There are versions for handling small, single-user workloads to large multi-user
workloads. SQLServer uses all SQL query language.
MySQL is the most popular open-source SQL relational database management system. It
is developed, distributed, and supported by Oracle Corporation. It is quite popular for use in
web applications. In fact MySQL can run on a desktop or laptop, alongside other applications, or
on web servers. Plenty of paid and free open-source applications that need a full-featured
DBMS use MySQL. Some applications and websites that use MySQL are: Joomla, WordPress,
Drupal, Google, Twitter, and YouTube. For most operating systems, it is necessary to download
MySQL Workbench to be able to use GUI functionality. Otherwise, users can only use the
command line functionality.
PostgreSQL is referred to as an object-relation database management system. It has
earned a reputation for its reliability, data integrity, and accuracy. It is compatible with all major
operating systems and has full support for foreign keys, joins, view, trigger and stored
procedures. PostgreSQL is available for free to use, modify, and distribute in open or closed
source.
Ingres Actian Vector Express is a RDNMS that started from a research project at UC
Berkley. It operates on Windows, Linux, most UNIX, and OpenVMS. It has some object-oriented
database features and it uses some forms of QUEL for queries and transactions in addition to
SQL.
We hope to create the type of queries that are representative of those that are typically
used by modern database administrators.
Lab 5: SQL Queries 2: Employees Database
The objective of this lab is to give students experience with more advanced SQL queries using
aggregation functions, subqueries, UPDATE, and INSERT statements with joins.
Some Examples to get You Started
The above database is a simple design for a sales database. The database consists of four tables: client,
purchase, purchase_item, and product. We will use this database to illustrate some more advanced
types of sql queries.
Aggregation Functions
You may want to summarize some quantity in a table based on another attribute. SQL has a number of
functions that allow you to do this within a SELECT statement using the GROUP BY clause. Some popular
aggregation functions available in SQL include the following:
MIN
returns the smallest value in a given column
MAX
returns the largest value in a given column
SUM
returns the sum of the numeric values in a given column
AVG
returns the average value of a given column
COUNT
returns the total number of values in a given column
COUNT(*)
returns the number of rows in a table
As an example, from the above sales database, we would like to know the average quantity that clients
order for each product in the database. To do this, we could use the following query:
SELECT pi.product_id AS ID, p.name AS Name, AVG(pi.amount) AS avg_quantity
FROM purchase_item AS pi
INNER JOIN product AS p ON pi.product_id = p.product_id
GROUP BY ID, Name;
Notice that both the product_id and product name are in the GROUP BY clause in this statement. This is
because in order to use an aggregate function, all other columns in the SELECT statement that are not
part of the aggregate function need to appear in the GROUP BY function.
Subqueries
In some cases, you may need to perform sub queries to get your answer from the database. For
example, in the above database, let’s say that we want to select purchases of a quantity greater than
100 of any product in the database. We could include the subquery in the where clause as is seen in the
following example. This type of subquery is typically called a nested subquery.
SELECT p.product_id, p.name
FROM products AS p
WHERE p.product_id IN (
SELECT pi.product_id
FROM purchase_item AS pi
WHERE pi.amount > 100);
Nested queries are an alternative to a join. For example, the above query could have been
accomplished using an inner join as follows:
SELECT p.product_id, p.name
FROM products AS p
INNER JOIN purchase_item AS pi ON p.product_id = pi.product_id
WHERE pi.amount > 100;
Another type of subquery places the subquery in the FROM clause. This type of subquery is typically
referred to as an inline view. For example, if we wanted to calculate the sum of the quantity of every
product sold, we would use an inline view as follows:
SELECT product.name, subquery1.total_amt
FROM products,
(SELECT pi.product_id AS product_id, SUM(pi.amount) AS total_amt
FROM purchase_item
GROUP BY pi.product_id) subquery1
WHERE subquery1.product_id = product.product_id;
Notice in this query that we are applying an aggregation function SUM in the subquery. Also notice that
at the end of the subquery we are giving it the name subquery1. This allows us to access items in the
subquery from the main query. For example, SUM(pi.amount) is referred to as total_amt in the
subquery and it is accessed as subquery1.total_amt in the main query. We also see this in the where
clause where we refer to subquery1.product_id.
Subqueries can also be included in the SELECT clause itself. This type of subquery is typically used when
you want to retrieve an aggregate function such as sum, min, max, or count. For example, if we wanted
to find the most expensive product in our database, we could use the following query:
SELECT p1.name, p1.price,
(SELECT MAX(p2.price)
FROM products AS p2
WHERE p1.product_id = p2.product_id) subquery2
FROM products p1;
Getting single values along with their attributes can sometimes be difficult. For example if you would
like to get the max of the count of products that were sold in quantities greater than 10, it can prove to
be a very complex query to get the max value along with the product name. You could do something
like the following but it will only return the max value:
SELECT MAX(subquery.counted) FROM(
SELECT p.name, pcount(*) AS counted FROM purchase_invoice AS pi
INNER JOIN products AS p ON pi.product_id = p.product_id
WHERE pi.amount > 10
GROUP BY p.name)subquery
SQL Server has a very nice convention to deal with this problem. You can easily avoid having to use an
MAX function on a subquery by using the TOP clause where TOP 1 will return the top record in the
result. To get the max, we simply need to ORDER BY DESC (order by descending) so that the resulting
query would look like this:
SELECT TOP 1 p.name, pcount(*) AS counted FROM purchase_invoice AS pi
INNER JOIN products AS p ON pi.product_id = p.product_id
WHERE pi.amount > 10
GROUP BY p.name
ORDER BY counted DESC;
Alternatively, if you wanted to use the MIN function, you could just remove the DESC from the ORDER
BY clause.
Inserts and Updates
To add records to a database, you can use the INSERT statement. For example, if we want to add new
client to the sales database, we could do the following (this example assumes that the client_id is autogenerated so we do not supply an ID value):
INSERT INTO client VALUES (‘Mike Mc’,’mmc@university.edu’)
Now let’s say that we want to add a new record and the client_id is not auto-generated. We would do
something like the following:
DECLARE @id int;
SELECT @id = MAX(c.client_id)+1 FROM client AS c;
INSERT INTO client VALUES (@id, ‘Mike McGuire, ‘mmcguire@towson.edu’);
Notice in the above example, that we have used the DECLARE clause to declare a variable named @id.
The @ symbol is the SQL convention for signifying a local variable. Then in the next line, we SELECT into
the @id variable the MAX of the client_id incremented by 1.
To change records in a database, we use the UPDATE statement. For example, if we want to update the
above client’s email address, we would do the following:
UPDATE client AS c
SET c.email = ‘mmc@gmail.com’
WHERE c.name = ‘Mike Mc’
For a more complex update, let’s say that a client wants to change the quantity of an order from 1 to 2.
We would do the following:
UPDATE purchase_item AS pi
INNER JOIN purchase AS p ON pi.purchase_id = p.purchase_id
INNER JOIN client AS c ON p.client_id = c.client_id
SET pi.amount = 2
WHERE c.name = ‘Mike McGuire’;
The above example is probably not realistic because Mike McGuire is likely to have more than one order
in the database.
Exercise
The following SQL exercise will use the employees database that you have created in SQL Server. There
are 9 queries some of which require SQL ninja skills.
For the following queries, when appropriate, limit the query result to give the exact answer (i.e. do not
provide a table of results and rely on user interpretation).
1. How many employees in the database have the last name Dredge?
2. Give the first name, last name, hire date, title, and salary of the first employee hired by the
company.
3. Give the above employee’s title and salary history including the from_date, to_date, title, and
salary for each unique record in the salary table.
4. What is the average salary for an Assistant Engineer?
5. Which department has the highest average salary?
6. Which department currently has the largest number of employees?
7. Georgi Facello has just become the manager for the Development Department. Update the
database to reflect this change.
8. Give all assistant engineers a 2% raise
9. Insert a new employee into the database with the following information
birth_date: 01/01/1977
first_name: John
last_name: Smith
gender: M
hire_date: 2013/09/12
title: Senior Engineer
from_date: 2013/09/12
to_date: present (how is this represented in the database?)
salary: 105000
from_date: 2013/09/12
to_date: present
He is the current manager of the Research Department.
Lab 7: SQL Server Transactions and Locking
The objective of this lab is to give students experience with transactions and locking.
This lab is a more tutorial-based lab and instead of providing examples designed to help you with the
exercise, you are meant to go through the lab step by step then complete an exercise designed to allow
you to experiment with transactions and locking.
The tutorial uses the AdventureWorks2014 database. If you have not installed this database, please
refer to Lab 2.
Transactions
As you know from Chapter 5 transactions have four key properties that are abbreviated ACID standing
for Atomic Consistent Isolated Durability. Atomic means that all the work in a transaction is a single unit
where either all of the instructions within a transaction happen or none of them happen. Consistent
means that after the transaction happens the data is “correct.” Isolation means that transactions can
run at the same time. Durability means that the results of a transaction will not impact the state of the
data if the transaction ends abnormally.
Transactions are particularly important when using any SQL statements that make changes to the data
such as INSERT, UPDATE, or DELETE. Using a transaction when performing these operation ensures ACID
properties in a multi-user environment. The following is a simple example of a transaction:
***********************DO NOT RUN THIS QUERY This is just an example *********************
DELETE FROM Production.ProductCostHistory
WHERE ProductID = 707;
***********************DO NOT RUN THIS QUERY This is just an example *********************
SQL Server will automatically treat this query as a transaction. This is called an autocommit transaction.
When the query is run, SQL server will write what it is going to do in the log file. Then, it proceeds with
the DELETE statement and writes in the log file what it has done. The use of autocommit transactions
degrades performance because every time this statement is run, it requires an entry to the log and thus
a disk I/O. In an explicit transaction, the DBA or application developer has control over when data is
committed to the database. In SQL, explicit transactions are started using the BEGIN TRANSACTION or
BEGIN TRAN statement.
BEGIN TRANSACTION
DELETE FROM Production.ProductionCostHistory
WHERE ProductID = 707
Let’s say that you begin a transaction and you decide that you want to restore the changes that you
have made before committing the transaction. In this case, instead of committing the transaction, you
can run the ROLLBACK TRANSACTION or ROLLBACK TRAN statement and the changes that you have
made will not be committed to the database. Now, in the same query window, delete the above SQL
code and enter the following:
ROLLBACK TRANSACTION;
If you are sure that you want to make the changes to the database, you can simply run the COMMIT
TRANSACTION or COMMIT TRAN statement. So to complete the above transaction we would simply run
the following:
BEGIN TRANSACTION
DELETE FROM Production.ProductionCostHistory
WHERE ProductID = 707
COMMIT TRANSACTION;
Database Locks
Another feature of a DBMS that ensures ACID properties of transactions is locking. Without locking, a
multi-user environment could not work as it ensures concurrency. Once again, as we know from Chapter
5 in the text, there are three basic lock types: shared, exclusive, and update. Locks can be inspected by
querying the sys.dm_tran_locks table such as this:
SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> ‘DATABASE’
The result of this query shows a table with the following three fields:
resource_type – This tells us what resource in the database the locks are being taken on. It can be one of
the following:
Resource
RID
Key
Object
Page
Extent
Table
DB
Description
Row identifier. Used to lock a single row within a table.
Row lock within an index. Used to protect key ranges in serializable transactions.
Individual object level of the lock
8 kilobyte(KB) data page or index page.
Contiguous group of eight data pages or index pages.
Entire table, including all data and indexes.
Database.
request_mode – This tells us the mode of our lock. Here is a list of request modes for SQL Server:
•
Intent shared (IS)- Protects requested or acquired shared locks on some (but not all) resources
lower in the hierarchy.
•
Intent exclusive (IX) – Protects requested or acquired exclusive locks on some (but not all)
resources lower in the hierarchy. IX is a superset of IS, and it also protects requesting shared locks
on lower level resources.
•
Shared with intent exclusive (SIX) – Protects requested or acquired shared locks on all resources
lower in the hierarchy and intent exclusive locks on some (but not all) of the lower level resources.
Concurrent IS locks at the top-level resource are allowed. For example, acquiring a SIX lock on a
table also acquires intent exclusive locks on the pages being modified and exclusive locks on the
modified rows. There can be only one SIX lock per resource at one time, preventing updates to
•
the resource made by other transactions, although other transactions can read resources lower
in the hierarchy by obtaining IS locks at the table level.
Intent update (IU) – Protects requested or acquired update locks on all resources lower in the
hierachy. IU locks are used only on page resources. IU locks are converted to IX locks if an update
operation takes place.
•
Shared intent update (SIU) – A combination of S and IU locks, as a result of acquiring these locks
separately and simultaneously holding both locks. For example, a transaction executes a query
with the PAGLOCK hint and then executes an update operation. The query with the PAGLOCK hint
acquires the S lock, and the update operation acquires the IU lock.
•
Update intent exclusive (UIX) – A combination of U and IX locks, as a result of acquiring these locks
separately and simultaneously holding both locks.
resource_description – This shows a brief description of the resource. Usually holds the id of the page,
object, file, row, etc. It isn’t populated for every type of lock
The filter on resource_type <> ‘DATABASE’ just means that we don’t want to see general shared locks
taken on databases. These are always present.
Shared Locks (S)
Shared locks are held on data being read when there is no intent to update it. The following shows an
example of a transaction that has a second query to show the resulting locks in the database.
BEGIN TRANSACTION
USE AdventureWorks2014
SELECT * FROM Person.Address
WHERE AddressId = 2
SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> ‘DATABASE’
ROLLBACK TRANSACTION;
Notice that in the bottom result, there are no locks visible as a result of this transaction. This is because
shared locks are automatically released unless other locking parameters are specified. If we want to see
the locks as a result of the transaction, we can use WITH (HOLDLOCK) at the end of the query like so:
BEGIN TRANSACTION
USE AdventureWorks2014
SELECT * FROM Person.Address WITH (HOLDLOCK)
WHERE AddressId = 2
SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <>‘DATABASE’
ROLLBACK TRANSACTION;
1) What locks to you see at each resource level?
Update Locks (U)
Update locks are a mix of shared and exclusive locks taken when data must be first read before it is
changed. The update lock is an indicator that the data may change or be deleted in the future. If the
data is actually modified, the DBMS will promote the update lock to an exclusive lock. You can also
force an update lock using WITH UPDLOCK as follows:
BEGIN TRANSACTION
USE AdventureWorks2014
SELECT * FROM Person.Address WITH (UPDLOCK)
WHERE AddressId < 2 SELECT resource_type, request_mode, resource_description FROM sys.dm_tran_locks WHERE resource_type <> ‘DATABASE’
ROLLBACK TRANSACTION
2) What locks to you see at each resource level?
Exclusive Locks (X)
Exclusive locks are used to lock data being modified by one transaction thus preventing modifications by
other concurrent transactions. The following shows an example of an exclusive lock as a result of an
UPDATE query:
BEGIN TRANSACTION;
USE AdventureWorks2014;
UPDATE Person.Address
SET AddressLine2 = ‘Test Address 2’
WHERE AddressId = 5;
SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> ‘DATABASE’;
ROLLBACK TRANSACTION;
3) What locks do you see at each resource level?
Intent locks (I)
Intent locks are a means in which a transaction notifies other transaction that it is intending to lock the
data. Thus the name. Their purpose is to assure proper data modification by preventing other
transactions to acquire a lock on the object higher in lock hierarchy. What this means is that before you
obtain a lock on the page or the row level an intent lock is set on the table. This prevents other
transactions from putting exclusive locks on the table that would try to cancel the row/page lock. In the
example we can see the intent exclusive locks being placed on the page and the table where the key is
to protect the data from being locked by other transactions.
BEGIN TRANSACTION;
USE AdventureWorks2014;
UPDATE TOP(5) Person.Address
SET AddressLine2 = ‘Test Address 2’
WHERE PostalCode = ‘98011’;
SELECT resou …
Purchase answer to see full
attachment

How it works

1. Paste your instructions in the instructions box. You can also attach an instructions file
2. Select the writer category, deadline, education level and review the instructions 
3. Make a payment for the order to be assignment to a writer
4.  Download the paper after the writer uploads it 

Will the writer plagiarize my essay?

You will get a plagiarism-free paper and you can get an originality report upon request.

Is this service safe?

All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades