Use the HR database for all the questions in this lab. For each item in this lab, paste the SQL you used to complete the task and the number of rows retrieved. Ensure you follow the coding standards listed in the ETSU SQL standards guide found on D2L. Save this document as a PDF and name it your_name_lab7.pdf
SELECT first_name, last_name, phone_number, salary
FROM hr.employees
WHERE salary > 10000;
Rows Retrieved: 15
Where “XX” is the first two letters of the first name, “yy” is the last two letters of the last name, “mm” is the month of hire while “dd” is the day of hire.
Display all employees full name and password.
SELECT DEPARTMENT_ID,DEPARTMENT_NAME FROM HR.DEPARTMENTS WHERE MANAGER_ID IS NULL;
SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=50 AND SUBSTR(HIRE_DATE,1,4)<1997;
SELECT HE.FIRST_NAME||' '||HE.LAST_NAME , START_DATE FROM HR.JOB_HISTORY HJ JOIN HR.EMPLOYEES HE ON HJ.EMPLOYEE_ID=HE.EMPLOYEE_ID WHERE TO_CHAR(START_DATE,'dd') = 15
SELECT FIRST_NAME||' '||LAST_NAME ,EMAIL||'@abcbank.com' FROM HR.EMPLOYEES
SELECT FIRST_NAME||' '||LAST_NAME ,PHONE_NUMBER,SALARY, (SALARY*0.05) AS TAX FROM HR.EMPLOYEES WHERE (SALARY*0.05)>500;
Where “XX” is the first two letters of the first name, “yy” is the last two letters of the last name, “mm” is the month of hire while “dd” is the day of hire.
Display all employees full name and password.
SELECT FIRST_NAME||' '||LAST_NAME , SUBSTR(FIRST_NAME,1,2)||SUBSTR(LAST_NAME, LENGTH(LAST_NAME)-2,2)||TO_CHAR(START_DATE,'mm')||TO_CHAR(START_DATE,'dd') FROM HR.EMPLOYEES
SELECT HE.EMPLOYEE_ID FROM HR.EMPLOYEES HE JOIN HR.JOBS HJ ON HE.JOB_ID=HJ.JOB_ID WHERE HJ.JOB_TILE IN ('IT programmer','Sales Rep') AND FIRST_NAME LIKE '_a%' AND LAST_NAME LIKE '%n%';
note: if u have any doubt, let me know.
Use the HR database for all the questions in this lab. For each item in this lab, paste the SQL y...
Please refer to the following business scenarios: (i) Using SELECT statements of SQL, find the employee id, first name, last name, job title and email of all employees working in Asia (i.e. all countries coming from the region ‘Asia’). This query must be implemented as a nested query! (ii) Using SELECT statements of SQL, find the employee id, first name, last name, job title and supervisor id of employees who had worked for more than 3 years and completed...
Write an SQL statement that will display the Employee ID, First name (only last 2 letters), phone number (replace . by /) of all employees whose job Id has a word ‘REP’.
Using the database: Write SQL queries for each of the questions below. 1. Find the media type which has 100 or more tracks. Print the name of such media type. Number of rows returned in the result = 3 A) Find the playlists which have one or more tracks that have never been purchased in California (CA). Print the Id, and the name of such playlists. Number of rows returned in the result = 18 B) Find the customers who...
database and sql problme THE QUESTIONS 3, 4,5 and 6 REFER TO THE RELATIONAL TABLES LISTED BELOW CREATE TABLE Department ( DECIMAL(5) VARCHAR(30) CHAR(5) DATE NOT NULL NOT NULL NOT NULL * Department number /*Department name * Department manager number */ /Manager start date DNumber DName Manager MSDate CONSTRAINT Department_PK PRIMARY KEY(DNumber) CONSTRAINT Department_CK UNIQUE(DName) CREATE TABLE DeptLocation DECIMAL(5) VARCHAR(50) NOT NULL NOT NULL DNumber * Department number */ * Department location */ Address CONSTRAINT DeptLocation_PK PRIMARY KEY(DNumber, Address) CONSTRAINT...
Use SQL to slove the problem 1. (7) List all condos in building C, the date they were cleaned, and the full name of the staff who cleaned them in August. 2. (5) Display the activities reserved in June for more than 3 people. Include the activity description and label the output as Activities Reserved in June. 3. (4) Listing for all guides and their certification renewal dates for next year. Include full name and hire date. 4. (6) Management...
Q.5] Answer the following questions based on the company database (based on the homework assignment 2) y database (based on the homework assignment 2) 3 IPage 1. For each department whose average employee salary is more than $30,000, write a SQL statement to retrieve the department name and the number of employees working for that department 2. Write a SQL statement to retrieve first name, last name, and address of each employee who lives in Houston, TX. 3. Write a...
Use the N2 Corporation database tables to design the following subqueries: a) Display all employees’ ID, names (Fname Lname format) and their department name who has the same department as Jinku Shaw’s department name. SQL File: https://pastebin.com/tR98LUb3
Quis -2 EMPLOYEES EMPLOYEE ID NUMBER(6.0 FIRST NAME VARCHAR NTE) CONSTRAINT EMPLASTNAME NN NOT NULL ENA LAST NAME VARCHAI CONSTRAINT EMPEMAIL NN NOT NULL ENABLE "EMAIL VARCHAR CHARZI29 BYTEL "PHONE NUMBE CONSTRAINT EMP HIRE DATE NN NOT NULL ENABLE "HERE DAT Aaue BYTE) CONS TRAINT EMPJOB NN NOT NULL ENABLE 3o8 MBER3 BYTE) SA OMMISSION PCT NUMBER2.23 "MANAGER 1D NUMBER(6.0), "DEPARTMENT ID NUMBER(4,0) 1. Return the names of employees who have the 2nd highest salary Sεutct 2. For every employee,...
For this set of Review Questions, we will create and use a database for the Wedgewood Pacific Corporation (WPC) that is similar to the Microsoft Access database we created and used in Chapters 1 and 2. Founded in 1957 in Seattle, Washington, WPC has grown into an internationally recognized organization. The company is located in two buildings. One building houses the Administration, Accounting, Finance, and Human Resources departments, and the second houses the Production, Marketing, and Information Systems departments. The...
Problem 33, chapter 7 from Database Systems 13th edition by Coronel Write a query to display the employee number, last name, email address, title, and department name of each employee whose job title ends in the word “ASSOCIATE.” Sort the output by department name and employee title Problem 36, chapter 7 from Database Systems 13th edition by Coronel Write a query to display the number of products within each base and type combination, sorted by base and then by type...