Question

0. Modify or create the table(file) named Staff first used in assignment 5. There is no change to the structure, and if you need to recreate it the column names, data types and lengths are described below: Table Name Column Name Data Type Length Comment/constraint Staff: id int name varchar office char fee decimal reviewDate date 3 primary key 15 not null 7 7,2 default 0.00 Initials have been added to the name column values, so data for an insert would be: (428,'Suh,S','JO 207',4567.80,'2019-12-20'), (533,'Brown,T', JO 238,3838.38, 2019-12-23), (803,'Creider,D', 'JO 216',7272.72, 2019-12-19'), (864,'Harter,D', JO 212,4343.43,NULL), (876,'Saffer S', JO 235,5775.33,'2019-12-19'), Show query results for steps 1 through 5 (parts a, b, and c): 1. Query Staff to project all columns for the name that SOUNDS LIKE 'Sue'. [The word SOUNDS is included to invoke a soundex function for words that sound alike but are spelled differently.] 2. Query the Staff table for a list of name and office values. Use the LOWER(string) function to show office in lower case letters. 3. Apply the CONCAT(str1str 2,...) function in a query to concatenate Staff name, a space, and review Date to be listed under the alias 'Faculty Reviews'. 4. Prepare a faculty directory by listing Staff names and offices. Apply the right pad function RPAD (string.length[,'chars'] -- to the string name using length 20 and fill with period characters. Use the alias "name" to replace the column heading with the RPAD call name.

Answer 1

CREATING TABLE:

create table Staff (id int(3) , name varchar(15) NOT NULL, office char(7), fee float(7,2) DEFAULT 0.00, reviewDate date, PRIMARY KEY(id));

INSERTING VALUES INTO TABLE:

INSERT INTO Staff VALUES (428,'Suh,S','JO 207',4567.80,'2019-12-20'),(533,'Brown,T','JO 238',3838.38,'2019-12-23'),(803,'Creider,D','JO 216',7272.72,'2019-12-19'),(864,'Harter,D','JO 212',4343.43,NULL),(876,'Saffer,S','JO 235',5775.33,'2019-12-19');

1)One of the many MySQL string functions is the SOUNDEX() function. This function returns a Soundex string from a given string. If two words sound the same, they should have the same Soundex string. If two words sound similar, but not exactly the same, their Soundex string might look similar but not exactly the same.An alternative query could have been constructed using SOUNDS LIKE instead of the SOUNDEX() function.

SELECT * FROM Staff WHERE name SOUNDS LIKE 'Sue';

2) SQL Server (Transact-SQL), the LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.

SELECT name,LOWER(office) FROM Staff;

3)CONCAT takes a variable number of string arguments and concatenates (or joins) them into a single string. It requires a minimum of two input values; otherwise, CONCAT will raise an error. CONCAT implicitly converts all arguments to string types before concatenation. CONCAT implicitly converts null values to empty strings.

SELECT CONCAT(name," ",reviewDate) AS FacultyReviews FROM Staff;

4)

The Oracle RPAD() function returns a string right-padded with specified characters to a certain length.The following illustrates the syntax of the Oracle RPAD() function:

RPAD(source_string, target_length [,pad_string]);

SELECT CONCAT(RPAD(name,20,"."),office) AS RPADcallname FROM Staff;