Using the following tables write a sql command:
List the full name and the total number of transactions handled by the full time
employee. Rank your results in descending order on the total number of transactions.
select concat(e.EFIRST," ",e.ELAST)as EMPLOYEENAME,e.EMPLOYEEID,
Count(t.TRANSCATIONID) as TOTAL TRANSACTIONS
from EMPLOYEES e
join TRANSACTIONS t ON t.EMPLOYEEID = e.EMPLOYEEID
where e.ETYPE = "FULL-TIME"
order by Count(t.TRANSCATIONID) desc;
/*The above query will give us the details about the Employee name, Employee ID and total no of transactions made by the FUll time employee. It will be ordered in Descending order*/
Sample output:
EMPLOYEENAME | EMPLOYEEID | TOTAL TRANSACTIONS |
Ahmad Raja | 2341 | 10 |
Kelly James | 1324 | 5 |
Ben Stokes | 2345 | 2 |
Using the following tables write a sql command: List the full name and the total number...
Lesson 10Create the DEPARTMENT tables based on the following: Column Name ID Name Data Type Number Varchar2 Length 7 25 Populate the DEPARTMENT table with data from the DEPT table. Include only columns that you need. Create the EMPLOYEE table based on the following table chart: Column Name ID LAST_NAME FIRST_NAME DEPT_ID Data Type Number Varchar2 Varchar2 Number Length 7 25 25 7 Modify the EMPLOYEE table to allow for longer employee last names. Confirm your modification. Confirm that both the...
I am using oracle sql developer to create some queries to generated reports and it is not working. I am not sure how to use count, group by, and order by. Help me fix my query and explain to me how you did, so can do it next time. Also, I have to convert this query to a stored procedure, so can you help me do it too? Here is my query: SELECT COUNT(GUEST.FIRSTNAME), GUEST.FIRSTNAME, GUEST.LASTNAME, GUEST.GUESTTYPE, RESERVATION.RESERVATIONDATE, GUEST.EMAIL, FROM...
Write Ten SQL SELECT statements to query the STUDENT schema you created for practice lab. 4. List all cities that have 10 or more students and instructors combined. Show city, state, number of student residents, number of instructor residents, and total student/instructor residents in that city. Sort by total in descending order. 5. List the instructor id and name of the instructors that teach fewer than 10 sections. 7. Find how many students are enrolled in sections taught by Todd...
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...
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...
Question 1.Write a SQL statement for each of the following questions based on the above tables (50 Points). 1) Create “Enrollment” table. 2) Change the credits of “Database” course from 2 to 3. 3) List the course titles (not Course_No) and grades John Doe had taken. 4) Calculate average of Café Balance, and name new field as Average Balance. 5) Create a roster for “Database” course (list student ID, last name, first name, major, and status for each student enrolled...
Write SQL statements to answer the following questions using Assignment 3’s schema (tables from part 1). 1- Find how many branches had have loans over $2000.00. 2- For each branch, find the most expensive loan. Your output should include Branch Id, loan amount for the highest loan for that Branch. 3- Find how many accounts there are for each customer. The output should include customer id and number of accounts for that customer. 4- Find the total balance amount of...
I am using Oracle SQL and am new to it. I have seven tables, one of them is a subtable of two of the others. I need to do the following queries: 1. List all Patients and what Bed they are assigned to 2. List all patients who had Treatments and what Treatment they received 3. List all patients who had tests and what Test they had 4. List the employees (doctors, nurses, etc.) who assisted each patient. 5. List...
can someone solve these quick please and thank you! sql chapter 4 Query #1: List the company name, contact name, contact title and the phone number for customers who HAVE NOT put in an order. Use an outer join. Query #2: Create a listing displaying the employee first name, last name and the full name (First name space Last Name) of the person they report to (Supervisor). This is a self-join. If an employee does not report to anyone then...
The Language Is SQL The language is SQL 13- List full details of agents with first names having exactly three or four letters sorted by the state attribute. 14- List the first name, the last name, the address, the city, the state, the branch number, and the email of agents working in the branch 8005 and having email addresses from yahoo.com. 15-List the first name, the last name, the address, the city, the state, the branch number, and the email...