Database Design Exam 1 – Practice Test with Answers.

Database Design Exam 1 – Practice Test with Answers. Use a select statement to display every field of table TESTDATA.EMPLOYEE. SELECT * FROM TESTDATA.EMPLOYEE; Write a query to display each department (DEPT) name in your TESTDATA.EMPLOYEE table. SELECT DEPT FROM TESTDATA.EMPLOYEE; This time display each department name only once. (Duplicate entries should not be displayed). Pay attention to miss spelled department names- if any) SELECT DISTINCT DEPT FROM TESTDATA.EMPLOYEE; Display today's date. Make sure it is displayed only once. SELECT SYSDATE FROM DUAL; Display each employee's name (first and last in one field) and pay rate (the amount of money s/he makes in a typical year) from TESTDATA.EMPLOYEE. Calculate columns, "Day's Wage" , "Monthly Wage" and "Hourly Wage". (Assume an eight hour workday, 30 days month, 365 days/year, and 2520 H/year) SELECT F_NAME || ' ' || L_NAME "NAME", (PAYRATE / 2520) "PAYRATE", (PAYRATE / 365) "DAILY_WAGE", (PAYRATE / 12) "MONTHLY_WAGE", PAYRATE "YEARLY_WAGE" FROM TESTDATA.EMPLOYEE Display the employee last names in one column and each employee's department concatenated with a space, a colon, another space, and their birth date in second column from TESTDATA.EMPLOYEE. Give this column an appropriate heading. SELECT L_NAME, DEPT || ' ' || ':' || ' ' || B_DATE "DEPT AND B_DATE" FROM TESTDATA.EMPLOYEE; Display the last name and address in one column, from the TESTDATA.EMPLOYEE table. In second field display the Employee's age (SYSDATE-birth date /365). Give the columns an appropriate heading. Pay attention to the way this expression is evaluated. SELECT L_NAME || ADDRESS "L_NAME AND ADDRESS", (SYSDATE - B_DATE) / 365 "AGE" FROM TESTDATA.EMPLOYEE; Display the name in one field (first and last) only for employee with id200; from TESTDATA.employee SELECT F_NAME || ' ' || L_NAME "NAME" FROM TESTDATA.EMPLOYEE WHERE ID200; Display the name in one field (first and last) and pay rate of each employee who makes less than 50,000 dollars or more than 70,000 dollars from TESTDATA.employee table. How many records are there? SELECT F_NAME || ' ' || L_NAME "Name", PAYRATE FROM TESTDATA.EMPLOYEE WHERE PAYRATE 50000 OR PAYRATE 70000; Display the name in one field (first and last) and pay rate of each employee who makes between 50,000 dollars and 70,000 dollars from TESTDATA.employee table. SELECT F_NAME || ' ' || L_NAME "Name", PAYRATE FROM TESTDATA.EMPLOYEE WHERE PAYRATE BETWEEN 50000 AND 70000; List the name (first and last) of employees without a phone number from TESTDATA.employee table. SELECT F_NAME || ' ' || L_NAME "NAME" FROM TESTDATA.EMPLOYEE WHERE PHONE IS NULL; Display each employee's name (first and last), address, and phone number. Provide a null value substitute for phone number in TESTDATA.employee table. If there is no phone number substitute it with 9 phone number SELECT F_NAME || ' ' || L_NAME "NAME", ADDRESS, NVL(PHONE, ) "PHONE" FROM TESTDATA.EMPLOYEE WHERE PHONE IS NULL; Display the name (first and last) and dept of each employee where SSN = or SSN = or SSN = or SSN = from TESTDATA.employee table. SELECT F_NAME || ' ' || L_NAME "NAME", DEPT FROM TESTDATA.EMPLOYEE WHERE SSN IN (,,); Display the name (first and last) and dept of each employee where their SSN is not one of: SSN = or SSN = or SSN = or SSN = from TESTDATA.employee table. SELECT F_NAME || ' ' || L_NAME "NAME", DEPT FROM TESTDATA.EMPLOYEE WHERE SSN NOT IN (,,); Display the name in one field (first and last) and pay rate of each employee who has a pay rate plus $2500 greater than 50,000 from TESTDATA.employee table. SELECT F_NAME || ' ' || L_NAME "NAME", PAYRATE FROM TESTDATA.EMPLOYEE WHERE (PAYRATE + 2500) 50000; Display the name in one field (first and last) and pay rate of each employee who has a pay rate plus $2500 greater than 50,000 and pay rate plus $2500 less than 70,000 from TESTDATA.employee table. SELECT F_NAME || ' ' || L_NAME "NAME", PAYRATE FROM TESTDATA.EMPLOYEE WHERE (PAYRATE + 2500) 50000 AND (PAYRATE + 2500) 70000; Show the structure of table TESTDATA.employee. DESCRIBE TESTDATA.EMPLOYEE; Show all the data in TESTDATA.employee table SELECT * FROM TESTDATA.EMPLOYEE; Display first name, last name, and address of all employees SELECT F_NAME, L_NAME, ADDRESS FROM TESTDATA.EMPLOYEE; Display all the data from TESTDATA.employee table for employees with no phone number. SELECT F_NAME || ' ' || L_NAME "NAME", PHONE FROM TESTDATA.EMPLOYEE WHERE PHONE IS NULL; Display all the data from TESTDATA.employee table for employees with a phone number. SELECT F_NAME || ' ' || L_NAME "NAME", PHONE FROM TESTDATA.EMPLOYEE WHERE PHONE IS NOT NULL; Display each employee's name (first and last in one field) and their phone number. Provide a null value substitute for phone number in TESTDATA.employee table. If there is no phone number substitute it with phone number. SELECT F_NAME || ' ' || L_NAME "NAME", NVL(PHONE, ) "PHONE" FROM TESTDATA.EMPLOYEE; Display the name (first and last in one field) of each employee whose last name begins with character 'K' or 'k' from TESTDATA.employee table. SELECT F_NAME || ' ' || L_NAME "NAME" FROM TESTDATA.EMPLOYEE WHERE L_NAME LIKE 'K%' OR L_NAME LIKE 'k%'; Display the name (first and last) of each employee whose last name does NOT begins with character 'K' or 'k' from TESTDATA.employee table. SELECT F_NAME || ' ' || L_NAME "NAME" FROM TESTDATA.EMPLOYEE WHERE L_NAME NOT LIKE 'K%' OR L_NAME LIKE 'k%'; Display the name (first and last in one field), and address of each employee with address in Frostburg. Remember Frostburg is part of the address. SELECT F_NAME || ' ' || L_NAME "NAME", ADDRESS FROM TESTDATA.EMPLOYEE WHERE LOWER(ADDRESS) LIKE '%frostburg%'; Display the name (first and last), and address of each employee with address NOT in Frostburg. SELECT F_NAME || ' ' || L_NAME "NAME", ADDRESS FROM TESTDATA.EMPLOYEE WHERE LOWER(ADDRESS) NOT LIKE '%frostburg%'; Display last name of employee with "son" as the last three characters of their last name. Such as Nelson, Johnson, Larson. SELECT L_NAME FROM TESTDATA.EMPLOYEE WHERE L_NAME LIKE '%son'; Display the name (first and last) of each employee with address in Frostburg or Fairfax. SELECT F_NAME || ' ' || L_NAME "NAME" FROM TESTDATA.EMPLOYEE WHERE LOWER(ADDRESS) LIKE '%frostburg%' OR LOWER(ADDRESS) LIKE '%fairfax%'; List Last name, phone number of any employee with the area code (the first three characters of phone number) of 301 SELECT L_NAME, PHONE FROM TESTDATA.EMPLOYEE WHERE PHONE LIKE '301%'; List Last name, phone number of any employee with the area code (the first three characters of phone number) of 301 AND the last two characters 33 SELECT L_NAME, PHONE FROM TESTDATA.EMPLOYEE WHERE PHONE LIKE '301%' AND PHONE LIKE '%33'; Use the string manipulation concept you learned today. Write a description of a query with the results. Make sure it is as complex as you can. Show your query to Dr. Chitsaz. SELECT F_NAME || ' ' || L_NAME "NAME", B_DATE, SSN, ADDRESS, DEPT, PAYRATE FROM TESTDATA.EMPLOYEE WHERE LOWER(ADDRESS) LIKE '%la vale%' AND SSN LIKE '234%' AND B_DATE LIKE '%84' AND LOWER(DEPT) LIKE 'management'; Write a query that displays employee's last name in lower case follow by first name in upper case letters in two columns. Use TESTDATA.employee table. Example: smith JOHN SELECT LOWER(L_NAME), UPPER(F_NAME) FROM TESTDATA.EMPLOYEE; Write a query that displays employee's last name in lower case follow by first name in upper case letters in one column. Use TESTDATA.employee table. Example: smith JOHN SELECT LOWER(L_NAME) || ' ' || UPPER(F_NAME) "Name" FROM TESTDATA.EMPLOYEE; Write a query that displays employee's last name in capital letters; departments in lower case; and pay rates. Use TESTDATA.EMPLOYEE table. Example: SMITH accounting 25000 SELECT UPPER(L_NAME), LOWER(DEPT), PAYRATE FROM TESTDATA.EMPLOYEE; Write a query to display the first character of names (first and last name in one field) in capital letter, the rest in lower case letter. Followed by the address of each employee. Example: John Smith 22 main st frostburg md 21532 SELECT INITCAP(F_NAME) || ' ' || L_NAME "NAME", LOWER(ADDRESS) "ADDRESS" FROM TESTDATA.EMPLOYEE; Write a query that displays only the first three digits of each employee's SSN, followed by employee name (first and last in one column). Use TESTDATA.EMPLOYEE table. Note: Although SSN is a number data type, system automatically cast it to character. So you will be able to use string manipulation functions. Example: 111 John Smith SELECT SUBSTR(SSN,1,3) "SSN", F_NAME || ' ' || L_NAME "NAME" FROM TESTDATA.EMPLOYEE; Write a query to display name (first and last) and the last four digits of their SSN using a function. Use TESTDATA.EMPLOYEE table. Example: John Smith 1111 SELECT F_NAME || ' ' || L_NAME "NAME", SUBSTR(SSN,-4) FROM TESTDATA.EMPLOYEE; Display the first 5 characters of last name followed by the first 10 characters of address followed by the department name in 6 characters field. Use TESTDATA.EMPLOYEE table. Example: Larso 111 Main S Accoun SELECT SUBSTR(L_NAME,1,5) "L_NAME", SUBSTR(ADDRESS,1,10) "ADDRESS", RPAD(DEPT,6) "DEPT" FROM TESTDATA.EMPLOYEE; Find the first position of character 'M' in the address; number of characters in address; the first 25 characters of address from Use TESTDATA.EMPLOYEE table SELECT INSTR(ADDRESS,'M') "M", LENGTH(ADDRESS) "SIZE", RPAD(ADDRESS,25)"ADDRESS" FROM TESTDATA.EMPLOYEE; Display the address without the first digital characters. Use TESTDATA.EMPLOYEE table For example if the address is: 12 Sun Dr. Oakland, MD 23456 Display it this way: Sun Dr. Oakland, MD 23456 SELECT LTRIM(ADDRESS,0) "ADDRESS" FROM TESTDATA.EMPLOYEE; Display the name (first and last), Pad it with character '-' in 50 fields from right, followed by the pay rate. Use TESTDATA.EMPLOYEE table. Example: John Smith---------------------- - 25000 SELECT RPAD(F_NAME || ' ' || L_NAME, 50, '-') || PAYRATE "NAME AND PAYRATE" FROM TESTDATA.EMPLOYEE; Display the last 5 characters of address, followed by the last four characters of SSN, followed by the first 6 characters of last name, followed by the middle two characters of SSN. Use TESTDATA.EMPLOYEE table. Example: Smith 22 Query: SELECT SUBSTR(ADDRESS,-5) || ' ' || SUBSTR(SSN,-4) || ' ' || SUBSTR(L_NAME,1,6) || ' ' || SUBSTR(SSN, 4,2) "INFO" FROM TESTDATA.EMPLOYEE; Display the phone number in this format -999-999-9999- SELECT '-' || SUBSTR(PHONE,1,3) || '-' || SUBSTR(PHONE,4,3) || '-' || SUBSTR(PHONE,-4) || '-' "PHONE" FROM TESTDATA.EMPLOYEE; 13. Display the employee data like this: first name, last name, payrate, payrate*3 SELECT F_NAME || ' ' || L_NAME || ' salary is $' || SUBSTR(PAYRATE,1,2) || ',' || RPAD(PAYRATE, 3, 0) || ' but would like to have $' || (PAYRATE * 3) "PAY" FROM TESTDATA.EMPLOYEE; Write a query that displays employee names (first and last) in 25 character fields padded with , followed by pay rate, followed by 5 , all in one field. Name this column SALARY_INFO. Make sure there is at least one blank space between the first and last name. Next three fields display pay per month, pay per month truncated with two decimals, pay per month rounded with two decimals Total of four columns. Use yee table. SELECT RPAD(F_NAME || ' ' || L_NAME, 25, '') || PAYRATE || '****' "SALARY_INFO", (PAYRATE/12) "PAY PER MONTH", TRUNC((PAYRATE/12), 2) "TRUNC PAY", ROUND((PAYRATE/12), 2) "ROUND PAY" FROM TESTDATA.EMPLOYEE; Write a query that displays the results of 4 powers of 3. Make sure to use DUAL table. SELECT POWER(4,3) FROM DUAL; Write a query to find the result of this expression: 987 MOD 31. SELECT MOD(987,31) FROM DUAL; Display employee's last name followed by '--- $' and the rounded value of their pay rate /12 in one field. Use yee table. Give an appropriate title to this column. SELECT L_NAME || '--- $' || ROUND((PAYRATE/12), 2) "EMPLOYEE PAY" FROM TESTDATA.EMPLOYEE; Write a query to display last name, yearly salary (payrate) of employees. Followed by a column with the lower three digits of yearly salary set to zero; example: 24567.54 as 24000). Use yee table. SELECT L_NAME, PAYRATE, TRUNC(PAYRATE, -3) FROM TESTDATA.EMPLOYEE; Write a query that displays last name, yearly salary, monthly salary, daily salary of employees. Assume year is 365 days. Followed by three columns with the lower two digits of yearly salary, monthly salary, daily salary set to zero; Use yee SELECT L_NAME, PAYRATE, (PAYRATE/12), (PAYRATE/365), TRUNC(PAYRATE, -2), TRUNC((PAYRATE/12), -2), TRUNC((PAYRATE/365), -2) FROM TESTDATA.EMPLOYEE; Display employee's last name, ID, ID in five digits fields, and ID in five character field. (Once using the number field, once using character field). You can tell the number field from character field the way the data is displayed. Character field is left justified, number field is right justified. (You may add ID+10,000) SELECT L_NAME, (ID + 10000), TRUNC((ID + 10000), 5), TRIM((ID +10000)) FROM TESTDATA.EMPLOYEE; Display employee's ID in six fields, followed by ' ', department in one field. Next two columns are: last 4 digits of SSN are set to zero, and pay rate with three lower numbers set to zero. SELECT RPAD(ID, 6 , '0') || ' ' || DEPT, TRUNC(SSN, -4), TRUNC(PAYRATE, -3) FROM TESTDATA.EMPLOYEE; We would like to use only number functions. Display SSN, and the first 4 digits of SSN. (You may want to divide SSN by 100,000, and truncate it). SELECT SSN, TRUNC((SSN/100000)) FROM TESTDATA.EMPLOYEE WHERE LENGTH(SSN) = 9 Redo question number 9 using character manipulation. SELECT SSN, RPAD(SSN, 4) FROM TESTDATA.EMPLOYEE; Display employee's name, pay rate, pay rate with 2% raise, pay rate with 5% raise, and pay rate with 10% raise. SELECT F_NAME || ' ' || L_NAME "NAME", PAYRATE, (PAYRATE .02) + PAYRATE, (PAYRATE .05) + PAYRATE, (PAYRATE *.1) + PAYRATE FROM TESTDATA.EMPLOYEE; Display the last 3 characters of SSN from yee using only number manipulation? SELECT SSN, MOD(SSN, 1000) FROM TESTDATA.EMPLOYEE WHERE LENGTH(SSN) = 9; Display the day, month, year of SYSDATE in the following formats☹ Make sure to label each column. DD-MM-YY DD-MM-YYYY DY-MON-YYY DAY, MONTH, YEAR DD-MM-YYYY HH:MI:SS SELECT TO_CHAR(SYSDATE,'DD-MM-YY') "DD- MM-YY", TO_CHAR(SYSDATE,'DD-MM-YYYY') "DD-MM-YYYY", TO_CHAR(SYSDATE,'DY-MON-YYY') "DY-MON- YYY", TO_CHAR(SYSDATE,'DAY,MONTH,YEAR') "DAY,MONTH,YEAR", TO_CHAR(SYSDATE,'DD-MM-YYYY HH:MI:SS') "DD-MM-YYYY HH:MI:SS" FROM TESTDATA.EMPLOYEE; Display the day, hours, minutes, and seconds of the birthdate in four fields (columns) from yee. Why the data are in this way? Please explain. Also make sure to label each field appropriately. SELECT TO_CHAR(B_DATE, 'DD') "DD", TO_CHAR(B_DATE, 'HH') "HH", TO_CHAR(B_DATE, 'MM') "MM", TO_CHAR(B_DATE, 'SS') "SS" FROM TESTDATA.EMPLOYEE WHERE B_DATE IS NOT NULL Display the list of employee's last name, with the birthday, month of birthday, year of birthday in four fields (choose your own format). SELECT L_NAME, B_DATE, TO_CHAR(B_DATE, 'MONTH') "B_MONTH", TO_CHAR(B_DATE, 'YEAR') "BIRTHYEAR" FROM TESTDATA.EMPLOYEE WHERE B_DATE IS NOT NULL; Display employee's name (first and last in one field) and the year they born. SELECT F_NAME || ' ' || L_NAME "NAME", TO_CHAR(B_DATE, 'YEAR') "BIRTHYEAR" FROM TESTDATA.EMPLOYEE WHERE B_DATE IS NOT NULL; Display employee's name ( first and last) and the month they born only for those who born in 'feb' (February) SELECT F_NAME || ' ' || L_NAME "NAME", TO_CHAR(B_DATE, 'MONTH') "BIRTHMONTH" FROM TESTDATA.EMPLOYEE WHERE TO_CHAR(B_DATE, 'MON') = 'FEB'; Display list of employees born in 'FEB' (February) and have payrate is less than $80,000. How many are there? Use to_char function. SELECT F_NAME || ' ' || L_NAME "NAME", TO_CHAR(B_DATE, 'MONTH'), PAYRATE FROM TESTDATA.EMPLOYEE WHERE TO_CHAR(B_DATE, 'MON') = 'FEB' AND PAYRATE 80000; We would like to know the birth date of our employees two month in advance. Display the department, name (first and last), birthday, birthday minus two months, in four columns. SELECT DEPT, F_NAME || ' ' || L_NAME "NAME", B_DATE, ADD_MONTHS(B_DATE, -2) "B_DATE IN ADVANCE" FROM TESTDATA.EMPLOYEE WHERE B_DATE IS NOT NULL; Display, birthday, birthday+4 months, SELECT B_DATE, ADD_MONTHS(B_DATE, 4) "+ 4 birthday+4 days, birthday+4 years. MONTHS", B_DATE + INTERVAL '4' DAY "+ 4 DAYS", B_DATE + INTERVAL '4' YEAR "+ 4 YEARS" FROM TESTDATA.EMPLOYEE WHERE B_DATE IS NOT NULL; Display today's year, month, day, SELECT TO_CHAR(SYSDATE, 'YY') "YEAR", hours, minutes, and seconds use the TO_CHAR(SYSDATE, 'MM') "MONTH", number dates only, each one in a TO_CHAR(SYSDATE, 'DD') "DAY", different field. Label the fields TO_CHAR(SYSDATE, 'HH') "HOUR", appropriately. TO_CHAR(SYSDATE, 'MM') "MINUTES", TO_CHAR(SYSDATE, 'SS') "SECONDS" FROM DUAL;