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

ID: 3694734 • Letter: I

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. 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

USE [master]

CREATE DATABASE [db_fdms_sbl_new] ON PRIMARY

( NAME = N'db_fdms_sbl_new', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAdb_fdms_sbl_new.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'db_fdms_sbl_new_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAdb_fdms_sbl_new_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

The given the default sql server installed path till data.

________________________________________________________________________

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;

________________________________________________________________________________________

Example:

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

Part 2

/ START SQL Creates a table named ‘PLAYER_ATTRIBS’ with a player number, name on their jersey, jersey number, and number of all-star appearances /

/* Per above Statement requirement below Create Table PLAYER_ATTRIBS is having wrong data types for NAME_ON_JERSEY and ALL_STAR_APPEARANCES
Correct data types to be for NAME_ON_JERSEY VARCHAR(100) and for ALL_STAR_APPEARANCES INT
based on the above SQL Create Statement below statement modified accordingly.
*/
CREATE TABLE PLAYER_ATTRIBS ( PLAYERNUM INT, NAME_ON_JERSEY VARCHAR(100), JERSEY_NUM INT, ALL_STAR_APPEARANCES INT );

-- Inserts data into the PLAYER_ATTRIBS table
/* PLAYER_NUM is not valid column in PLAYER_ATTRIBS table.
Hence valid column name per create statement is PLAYERNUM
Insert statement Syntax gives error. To correct this error update the correct column name
*/
-- below insert values are not in a sequence per column mentioned in the insert statement. Hence Corrected accordingly
INSERT INTO PLAYER_ATTRIBS(PLAYERNUM, NAME_ON_JERSEY, JERSEY_NUM, ALL_STAR_APPEARANCES) VALUES ( 8366,'Animal', 99, 0);

/*Below Insert statement is error. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement
Hence last value 2 is removed from the Values.
*/
INSERT INTO PLAYER_ATTRIBS(PLAYERNUM, NAME_ON_JERSEY, JERSEY_NUM, ALL_STAR_APPEARANCES) VALUES (2007, 'Slugger', 20, 1);
INSERT INTO PLAYER_ATTRIBS(PLAYERNUM, NAME_ON_JERSEY, JERSEY_NUM, ALL_STAR_APPEARANCES) VALUES (1779, 'GEORGE HERMAN “BABE” RUTH', 3, 2);
INSERT INTO PLAYER_ATTRIBS(PLAYERNUM, 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
-- Per above requirement of atleast 1 all star this means including 1 and above
SELECT NAME_ON_JERSEY FROM PLAYER_ATTRIBS WHERE ALL_STAR_APPEARANCES >= 1;

-- Selects the names of all players with a jersey number of 20
/*Wrong Column Name PLAYERNAME mentioned in the Select Statement which gives syntax error.
Table name also wrong PLAYER. Correct table name is PLAYER_ATTRIBS.
Correct column name NAME_ON_JERSEY and Correct table name PLAYER_ATTRIBS and the error will automatically resolved
*/
SELECT NAME_ON_JERSEY FROM PLAYER_ATTRIBS WHERE JERSEY_NUM = 20;

-- Selects the jersey numbers of all players whose name is Animal
-- Column name NAME_ON_JERSEY is string type. we have to mentioned the value with in the single quotes.
-- Single quotes to be included starting and ending of the value as 'Animal'
SELECT JERSEY_NUM FROM PLAYER_ATTRIBS WHERE NAME_ON_JERSEY = 'Animal';

-- Selects a list of jersey numbers, listing each number only once
-- Distinct means eliminating repeated values and list the unique value from the table. Hence does not required where condition as
SELECT DISTINCT JERSEY_NUM FROM PLAYER_ATTRIBS;

-- Selects a list of jersey numbers, with the count of how many players have that number
-- Use JERSEY_NUM in group by clause and with in the count should have JERSEY_NUM. Group by is different words should not be combined.
SELECT DISTINCT JERSEY_NUM, COUNT(JERSEY_NUM) FROM PLAYER_ATTRIBS GROUP BY JERSEY_NUM;

-- Updates the player whose jersey name is Animal to have 1 all-star appearance
/* per update requirement there couple information are wrong in below update statement
1. Set column name should be before where condition
2. Column name ALL_STAR_APPERANCES is wrong it should be ALL_STAR_APPEARANCES
3. SQL Server is not a case sensitive but for standard we use the same case as it was data in the database or convert to UPPER OR LOWERR
*/
UPDATE PLAYER_ATTRIBS SET ALL_STAR_APPEARANCES =1 WHERE NAME_ON_JERSEY='Animal' ;

-- Selects all jersey names in alphabetical order from A to Z
/* Per requirement following information are wrong,
1. Column name NAME is mentioned wrong which is not correct column per table. Correct column is NAME_ON_JERSEY
2. Order By used with DESC this is descending Order, Per your requirement it should be Ascending Order
*/
SELECT NAME_ON_JERSEY FROM PLAYER_ATTRIBS ORDER BY NAME_ON_JERSEY ASC;

Navigate

Introduction to SQL. Please help First please go the link below it show the cont

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

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

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

Question

Explanation / Answer

Related Questions

Navigate