Introduction to SQL: Module Three Exercise Guidelines and Rubric Being able to r

Question

Introduction to SQL: Module Three Exercise Guidelines and Rubric

Being able to read SQL statements, understanding how they work, and recognizing when they do not do what they are supposed to do are critical parts of working with databases. There will also be times when you will come across code that is complex and is not documented well (or at all.)

Your Assignment

Part 1: The following SQL code adds to the tables already in the db_fdms_sbl database. These statements create additional tables and data for the baseball database and then update and query against it. Log in to the database from the SQL Server Management Studio (SSMS) to see the rest of the tables available. Execute these statements one at a time.

In a Word document, for each statement, identify and explain the purpose of each of the key syntax elements.

-- First write and execute the statement to create your second personal profile database and name it db_fdms_sbl_new

-- This is your personal profile copy of the baseball database. We will copy/create all of the tables in Milestone Three.

-- So, now you should have two personal profile databases (db_fdms_ba_new and db_fdms_sbl_new).

USE db_fdms_sbl_new;

CREATE TABLE BATTING_STATS (

PLAYERNUM INT,

HITS INT,

HOMERUNS INT,

COMMENTS VARCHAR(50)

);

CREATE TABLE PITCHING_STATS (

PLAYERNUM INT,

WINS INT,

LOSSES INT

);

INSERT INTO BATTING_STATS(PLAYERNUM, HITS, HOMERUNS, COMMENTS) VALUES (8366, 10, 4, ‘Power Hitter’);

INSERT INTO BATTING_STATS(PLAYERNUM, HITS, HOMERUNS, COMMENTS) VALUES (2007, 1, 0, ‘Rookie’);

INSERT INTO BATTING_STATS(PLAYERNUM, HITS, HOMERUNS, COMMENTS) VALUES (1779, 524, 50, ‘All Star’);

INSERT INTO BATTING_STATS(PLAYERNUM, HITS, HOMERUNS, COMMENTS) VALUES (6564, 3000, 500, ‘Hall of Famer’);

INSERT INTO PITCHING_STATS(PLAYERNUM, WINS, LOSSES) VALUES (5410, 4, 2);

INSERT INTO PITCHING_STATS(PLAYERNUM, WINS, LOSSES) VALUES (8366, 10, 5);

INSERT INTO PITCHING_STATS(PLAYERNUM, WINS, LOSSES) VALUES (523, 10, 10);

INSERT INTO PITCHING_STATS(PLAYERNUM, WINS, LOSSES) VALUES (6564, 0, 16);

SELECT PLAYERNUM FROM BATTING_STATS WHERE HITS > 7;

UPDATE BATTING_STATS SET COMMENTS=’Up and Coming Star’ WHERE PLAYERNUM = 8366;

SELECT COUNT(*) FROM BATTING_STATS WHERE COMMENTS=’All Star’;

SELECT * FROM BATTING_STATS WHERE COMMENTS != ‘Hall of Famer’ ORDER BY HITS ASC;

SELECT PLAYERNUM,WINS,LOSSES FROM PITCHING_STATS ORDER BY WINS,LOSSES DESC;

Part 2:

The following SQL code adds to the tables already in your personal profile account database. These statements create additional tables and data for the baseball database and then update and query against it. Log in to the database from the SSMS to see the rest of the tables available. In a Word document, for each statement, identify any syntax and/or logic errors and make the appropriate corrections.

The following also shows you how to properly annotate/document your code using the multi-line comment /* …. */ and inline comment -- (double dashes).

/* START SQL

Creates a table named ‘PLAYER_ATTRIBS’ with a player number, name on their jersey,

jersey number, and number of all-star appearances

*/

CREATE TABLE PLAYER_ATTRIBS (

PLAYERNUM INT,

NAME_ON_JERSEY INT,

JERSEY_NUM INT,

ALL_STAR_APPEARANCES VARCHAR(100)

);

-- Inserts data into the PLAYER_ATTRIBS table

INSERT INTO PLAYER_ATTRIBS(PLAYER_NUM, NAME_ON_JERSEY, JERSEY_NUM, ALL_STAR_APPEARANCES) VALUES ( ‘Animal’, 8366, 99, 0);

INSERT INTO PLAYER_ATTRIBS(PLAYER_NUM, NAME_ON_JERSEY, JERSEY_NUM, ALL_STAR_APPEARANCES) VALUES (2007, ‘Slugger’, 20, 1, 2);

INSERT INTO PLAYER_ATTRIBS(PLAYER_NUM, NAME_ON_JERSEY, JERSEY_NUM, ALL_STAR_APPEARANCES) VALUES (1779, ‘GEORGE HERMAN “BABE” RUTH’, 3, 2);

INSERT INTO PLAYER_ATTRIBS(PLAYER_NUM, NAME_ON_JERSEY, JERSEY_NUM, ALL_STAR_APPEARANCES) VALUES (523, ‘Basher’, 20, 5);

-- Selects the jersey names for players with AT LEAST 1 all-star appearance

SELECT NAME_ON_JERSEY FROM PLAYER_ATTRIBS WHERE ALL_STAR_APPEARANCES > 1;

-- Selects the names of all players with a jersey number of 20

SELECT PLAYERNAME FROM PLAYER WHERE JERSEY_NUM = 20;

-- Selects the jersey numbers of all players whose name is Animal

SELECT JERSEY_NUM FROM PLAYER_ATTRIBS WHERE NAME_ON_JERSEY = Animal;

-- Selects a list of jersey numbers, listing each number only once

SELECT DISTINCT JERSEY_NUMS FROM PLAYER_ATTRIBS WHERE JERSEY_NUM != JERSEY_NUM;

-- Selects a list of jersey numbers, with the count of how many players have that number

SELECT DISTINCT JERSEY_NUM, COUNT(*) FROM PLAYER_ATTRIBS GROUPBY PLAYERNUM;

-- Updates the player whose jersey name is Animal to have 1 all-star appearance

UPDATE PLAYER_ATTRIBS WHERE NAME_ON_JERSEY=’ANIMAL’ SET ALL_STAR_APPERANCES=1;

-- Selects all jersey names in alphabetical order from A to Z

SELECT NAME FROM PLAYER_ATTRIBS ORDER BY NAME_ON_JERSEY DESC;

-- END SQL

Include the following critical elements:

1. SQL Statements Explained: All critical elements for each statement in Part 1 should be documented and explained clearly.

2. Error Identification: The error(s) in each statement in Part 2 should be identified correctly.

3. Error Corrections: Each statement in Part 2 should be corrected to work as expected.

Explanation / Answer

I ma not very clear about the question . what do you want clear explanation of queries? I think that. so according to my understanding I am answering.

1.Display thosePLAYERNUM which has HITS more than 7 from the table BATTING_STATS

update the table BATTING_STATS whenever there is PLAYERNUM which is equals to 8366 and change the COMMENTS 'Up and coming star'

selects total number of players where COMMENTS='All Star'

display total number of records from  BATTING_STATS acoording to ascending order of HITS where COMMENTS!=Hall of famer

select playernumber,number of wins and losses from PITCHING_STATS according to descending order of number of wins and losses

2. according to the created table player number and jersey name is int but when you are inserting values player number is character and others as int where last field is varchar. so there is mismatch

first of all there are 4 attributes but you are iserting values for 5 attributes moreover there is also mismach in datatype for the fields name on jersey and all star appearence

if the insertion is not write then how can query run?

if I consider now you can insert the values correctly the queries will be as follows:

SELECT NAME_ON_JERSEY FROM PLAYER_ATTRIBS WHERE ALL_STAR_APPERANCES IN (SELECT NAME_ON_JERSEY FROM PLAYER_ATTRIBS GROUP BY ALL_STAR_APPERANCES HAVING COUNT(ALL_STAR_APPEARANCES) > 1)

SELECT NAME_ON_JERSEY FROM PLAYER_ATTRIBS WHERE JERSEY_NUM = 20;

(NOTE:Is there any table named PLAYER?if so you have to join)

SELECT DISTINCT JERSEY_NUMS FROM PLAYER_ATTRIBS

UPDATE PLAYER_ATTRIBS SET ALL_STAR_APPERANCES=1 WHERE NAME_ON_JERSEY=’ANIMAL’;

SELECT NAME_ON_JERSEY

FROM PLAYER_ATTRIBS ORDER BY NAME_ON_JERSEY

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.