i want the answer

User Generated

UFB0008

Programming

University of North Texas

Description

Q1) Write a SQL query to fetch all the duplicate records from applicants table.

/**Tables Structure:**/

drop table applicants;

create table applicants

(

user_id int primary key,

user_name varchar(30) not null,

email varchar(50));

insert into users values

(1, 'pearson', 'pearson@gmail.com'),

(2, 'Reshma', 'reshma@gmail.com'),

(3, 'Farhana', 'farhana@gmail.com'),

(4, 'Robin', 'robin@gmail.com'),

(5, 'Robin', 'robin@gmail.com');

select * from applicants;

Q2) Create a SQL query to retrieve the employee table's second-to-last record.

--Tables Structure:

drop table employee;

create table employee

( emp_ID int primary key

, emp_NAME varchar(50) not null

, DEPT_NAME varchar(50)

, SALARY int);

insert into employee values(101, 'Mohan', 'Admin', 4000);

insert into employee values(102, 'Rajkumar', 'HR', 3000);

insert into employee values(103, 'Akbar', 'IT', 4000);

insert into employee values(104, 'Dorvin', 'Finance', 6500);

insert into employee values(105, 'Rohit', 'HR', 3000);

insert into employee values(106, 'Rajesh','Finance', 5000);

insert into employee values(107, 'Preet', 'HR', 7000);

insert into employee values(108, 'Maryam', 'Admin', 4000);

insert into employee values(109, 'Sanjay', 'IT', 6500);

insert into employee values(110, 'Vasudha', 'IT', 7000);

insert into employee values(111, 'Melinda', 'IT', 8000);

insert into employee values(112, 'Komal', 'IT', 10000);

insert into employee values(113, 'Gautham', 'Admin', 2000);

insert into employee values(114, 'Manisha', 'HR', 3000);

insert into employee values(115, 'Chandni', 'IT', 4500);

insert into employee values(116, 'Satya', 'Finance', 6500);

insert into employee values(117, 'Adarsh', 'HR', 3500);

insert into employee values(118, 'Tejaswi', 'Finance', 5500);

insert into employee values(119, 'Cory', 'HR', 8000);

insert into employee values(120, 'Monica', 'Admin', 5000);

insert into employee values(121, 'Rosalin', 'IT', 6000);

insert into employee values(122, 'Ibrahim', 'IT', 8000);

insert into employee values(123, 'Vikram', 'IT', 8000);

insert into employee values(124, 'Dheeraj', 'IT', 11000);

select * from employee;

Required Output: Vikram

Create a SQL query to only show the employee table's information for those with the only highest or lowest salaries across all departments.

--Tables Structure:

drop table employee;

create table employee

( emp_ID int primary key

, emp_NAME varchar(50) not null

, DEPT_NAME varchar(50)

, SALARY int);

insert into employee values(101, 'Mohan', 'Admin', 4000);

insert into employee values(102, 'Rajkumar', 'HR', 3000);

insert into employee values(103, 'Akbar', 'IT', 4000);

insert into employee values(104, 'Dorvin', 'Finance', 6500);

insert into employee values(105, 'Rohit', 'HR', 3000);

insert into employee values(106, 'Rajesh','Finance', 5000);

insert into employee values(107, 'Preet', 'HR', 7000);

insert into employee values(108, 'Maryam', 'Admin', 4000);

insert into employee values(109, 'Sanjay', 'IT', 6500);

insert into employee values(110, 'Vasudha', 'IT', 7000);

insert into employee values(111, 'Melinda', 'IT', 8000);

insert into employee values(112, 'Komal', 'IT', 10000);

insert into employee values(113, 'Gautham', 'Admin', 2000);

insert into employee values(114, 'Manisha', 'HR', 3000);

insert into employee values(115, 'Chandni', 'IT', 4500);

insert into employee values(116, 'Satya', 'Finance', 6500);

insert into employee values(117, 'Adarsh', 'HR', 3500);

insert into employee values(118, 'Tejaswi', 'Finance', 5500);

insert into employee values(119, 'Cory', 'HR', 8000);

insert into employee values(120, 'Monica', 'Admin', 5000);

insert into employee values(121, 'Rosalin', 'IT', 6000);

insert into employee values(122, 'Ibrahim', 'IT', 8000);

insert into employee values(123, 'Vikram', 'IT', 8000);

insert into employee values(124, 'Dheeraj', 'IT', 11000);

select * from employee;

Ex: Output: In Admit department

# emp_ID,emp_NAME,DEPT_NAME,SALARY ,max_salary,min_salary

113GauthamAdmin200050002000

120Monica Admin500050002000

Create a SQL query from the students table to swap the adjacent student names.

Note: The student name should remain the same if there are no adjacent students.

--Table Structure:

drop table students;

create table students

(

id int primary key,

student_name varchar(50) not null

);

insert into students values

(1, 'James'),

(2, 'Michael'),

(3, 'George'),

(4, 'Stewart'),

(5, 'Robin');

select * from students;

Q7) Get all the instances where Alaska experienced extremely low temperatures for three or more straight days from the weather table.

Note: When the weather is below zero, it is deemed to be extremely cold.

--Table Structure:

drop table weather;

create table weather

(

id int,

city varchar(50),

temperature int,

day date

);

delete from weather;

insert into weather values

(1, ‘Alaska’, -1, to_date('2021-01-01','yyyy-mm-dd')),

(2, ‘Alaska’, -2, to_date('2021-01-02','yyyy-mm-dd')),

(3, ‘Alaska’, 4, to_date('2021-01-03','yyyy-mm-dd')),

(4, ‘Alaska’, 1, to_date('2021-01-04','yyyy-mm-dd')),

(5, ‘Alaska’, -2, to_date('2021-01-05','yyyy-mm-dd')),

(6, ‘Alaska’, -5, to_date('2021-01-06','yyyy-mm-dd')),

(7, ‘Alaska’, -7, to_date('2021-01-07','yyyy-mm-dd')),

(8, ‘Alaska’, 5, to_date('2021-01-08','yyyy-mm-dd'));

select * from weather;


User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

Q1) Write a SQL query to fetch all the duplicate records from applicants table.
SELECT user_name, email, COUNT(*)
FROM user
GROUP BY user_name, email
HAVING COUNT(*) > 1;

Q2) Create a SQL query to retrieve the employee table's second-to-last record.
SELECT *
FROM employee
ORDER BY emp_ID DESC
LIMIT 1 OFFSET 1;

3. Create a SQL query to ...


Anonymous
Super useful! Studypool never disappoints.

Studypool
4.7
Indeed
4.5
Sitejabber
4.4

Related Tags