CSCI 133 Database Concepts (SQL) Assignment
6 Pages2218 Words304 Views
Database Concepts (SQL) (CSCI 133)
Added on 2019-09-16
CSCI 133 Database Concepts (SQL) Assignment
Database Concepts (SQL) (CSCI 133)
Added on 2019-09-16
ShareRelated Documents
CSCI133 - DATABASE CONCEPTS (SQL)Chapter 4 SQL WORKSHEET1. Display the name of each employee and the date they were hired. When displaying the nameof the employees, display the names in mixed case characters.2. Display the name of each employee and the person’s yearly salary rounded to the nearestthousand dollars. Note the SALARY in the table is the individual’s monthly salary.3. Display each employee’s name and monthly pay. If the employee is a salesman, theninclude the individual’s commission in the month pay, otherwise display display a dash (-)symbol.4. List Employees who's first names are four characters long. Display their Employee number,name and job title5. List the Departments and the cities they are located in. Format the department name sothat it is RIGHT padded with dots (...) and each name is 20 spaces long. Format the City thedepartment is located in so that it is LEFT padded with asterisks (***) and 15 characterslong.6. Display a list with the names of each employee, a single space and their department,enclosed in parenthesis as a single column. Order the list by the employee’s Job Title andthen Name.Example: SMITH (CLERK)7. Display a list of employees whose last name sounds similar to 'JANS'. Include both theirname and what the SOUNDEX value of their last name is.8. List all employees and their salaries and “TIER” column. If the employee makes less than$1000 per month, then give a comment of TIER 1. If an employee makes from $1000-$2000per month then TIER 2. If an employee makes more than $2000 then use a comment ofTIER 3.9. Put together a list of bonuses for all employees in department 30. List the employees nameand their bonus amount. Sort the list from highest to lowest bonus amount. Bonus will becalculated as (Salary divided by two) plus Commission. If the employee does not receive acommission then calculate their bonus as (Salary divided by two) plus $100.
10. List a unique list of Job Titles in the employee table, along with the number of characters inthe job title. Order the list with the job with the longest title at the top of the list.CSCI133 - DATABASE CONCEPTS (SQL)Chapter 4 CHAPTER EXAM1. A user needs to know which customer was referred by another customer whose customer#is 1006. List the last name with the first character capitalized and the rest of the last namelower case. List the first name in all lower case and the state in all upper case. The columnheadings should be Last, First, and ST. You do not know how the data is stored in the database.2. The company is going to raise the cost of the books by 23%. A user need a list of the booktitles, authors first and last name, and the new cost of those books that have a new costgreater than $50.00. The column headings should be Book Titles, Author Name, andNew Cost. The new cost should be displayed with a dollar sign and carried out to 3 decimalpositions. The list should be in ascending sequence on the author’s last name.3. A users needs to know how many characters are in the longest city name in the orderstable. The column heading should be City and # Characters. List each city only once. Sortthe list in descending sequence on # characters.4. Management is proposing to give each publisher a bonus on the profit generated for eachbook based on the following information: Publisher Id Percent 1 5% 2 10% 3 15% 4 20% 5 20%Profit is calculated as Retail less cost. Column heading should be ISBN, Title, Profit andBonus. Order the list by the Books Publisher ID Ascending, and then the Books ISBNAscending.
5. A user is developing a code for the Customers that live in NJ and NY. The code consists ofthe first 2 character of the customer’s first name, the last 3 characters of the customer’slast name, and the third and forth characters of the zip code. List the customer’s first name,last name, zip code, and the code. Column heading should be First Name, Last Name,Zip, and Code.CSCI133 - DATABASE CONCEPTS (SQL) Chapter 5 SQL WORKSHEET1. Display the name of each employee and how many months he or she has been employedwith the company. When displaying the name of the employees, display the names in mixedcase characters.2. Determine how long each employee has until retirement if an employee must remain withthe company for at least 25 years to be eligible for retirement benefits.3. If each employee must be with the company for at least 25 years to receive benefits,display the earliest possible retirement date for each employee. Display the retirement datein the same format as January 1, 2013.4. List Employees who were hired in 1980. Display their Employee number, name and job title5. List the Employees who were hired in February of any year.6. List the Employees, date they were hired, and their Salary. Format the date in the sameformat as 2013-11-22 and format their salary as dollars with two decimal places. Order thelist by the date they were hired.CSCI133 - DATABASE CONCEPTS (SQL) Chapter 5 CHAPTER EXAM1. Determine the calendar date of the next occurrence of Friday, based on today’s date. Thecolumn heading should be DAY. .2. An employee has worked for the company since December 03, 1968. How many monthshas the employee worked for the company? The column heading should be Months Worked.Round the answer to 0 decimal positions. HINT: DUAL3. A users needs a list of the books and dates they were published. They want the Title, ISBNand date published, and the date should be formatted in Month, Day Year format. Order thelist with the most recently published books first.
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
SQL Queries for Employee Database and Importance of Database Testinglg...
|14
|1593
|119