Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Review for Data Engineer > DDL||DML > Flashcards

DDL||DML Flashcards

1
Q

What is used in DML ?

A
  1. INSERT
  2. UPDATE
  3. DELETE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Write a query where you need to insert 2 rows in EMP_Parth table

Empid EMP_NAME. SALARY
101 ANU. 3000
102 Mahi 4000

A

Insert into EMP_PARTH
(EMPID, EMP_NAME, SALARY)
values(‘101’, ‘ANU’, ‘3000’);
Insert into EMP_PARTH
(EMPID, EMP_NAME, SALARY)
values(‘102’, ‘Mahi’, ‘4000’);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Write a query to update the EMP_NAME ‘ANU’ to ‘TANNU’ from EMP_PARTH table where EMP_ID = 101

Try to get the syntax for update sql

A

Update EMP_PARTH
Set EMP_NAME = ‘TANNU’
where EMP_ID=101;

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, …, columnN = valueN
[WHERE condition];

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is Dev Env ?

A

Development where the developer develop the code and do unit testing

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Write a query to concat first_name, middle_name, and last_name together and separate by ‘_’ between each column, take that column name as full_name from EMP_Parth table
Also write the query using pipe function

pipe and concat are the same thing but have different purposes. Pipe is more shorter queries but concat is used in more complex queries

A

Select concat(first_name,’ ‘,middle_name,’ ‘,last_name) AS full_name
from EMP_Parth;

Select first_name||’ ‘||Middle_name||’ ‘||last_name AS full_name
From EMP_Parth;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is Substr and the syntax ?

A

Extracts a substring from a string (starting at any position)

Substr( column_name, start_position, number of characters)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Write a query to display employee_ID, first_name, last_name, salary where first_name starts with A from employees table

A

Select employee_ID, first_name, last_name, salary
from employees
Where SUBSTR(first_name, 1,1) = ‘A’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Write a query to display employee_ID, first_name, last_name, salary where first_name ends with ‘a’ from employees table

A

Select employee_ID, first_name, last_name, salary
from employees
Where SUBSTR(first_name, -1,1) = ‘A’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Write a query to display employee_ID, first_name, last_name, salary where second letter of first_name is ‘r’ from employees table

A

Select employee_ID, first_name, last_name, salary
from employees
where SUBSTR( first_name, 2,1) = ‘r’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is INSTR and the syntax ?

A

It is the search string of a substring

INSTR( column_name, search_character, start_position, occurrence)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Write a query to display employee_ID, first_name, last_name from employees table where character ‘ a’ will be after position 2

A

Select employee_ID, first_name, last_name
from employees
Where INSTR(first_name, ‘a’,1)>2;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Write a query to display employee_ID, first_name, last_name from employees table where character ‘r’ will be after 3 position

A

Select employee_ID, first_name, last_name
from employees
Where INSTR(first_name, ‘r’,1)>3;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is Length, replace, and translate ? what are the syntax

A

Check your notes in pictures

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is Decode and what is the syntax ?

A

The SQL DECODE function is a database function used for conditional value replacement in a query result. It is primarily used in Oracle Database and a few other database systems. DECODE allows you to compare a value to a set of possible values and return a corresponding result when a match is found.

Decode( column_name, search1, result1, search2, result2, default_result)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is the syntax for left outer Join ?

A

Select Column_name
From table_name1 LEFT OUTER JOIN table_name2
On table_column1 = table_column2
Where condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is the syntax for right outer join ?

A

Select column_name
From table1_name RIGHT OUTER JOIN table2_name
ON table1_column = table2_column
Where condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is the syntax for Inner Join ?

A

Select column_name
From Table1_name Inner Join table2_name
On table1_column = table2_column
Where condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is the syntax for full outer join ?

A

Select column_name
From table1_name Full outer join Table2_name
On table1_column = table2_column
Where condition ;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Query to display Employee_id, First_name, Manager_id, Salary, Department_id, Department_name, all records from employees table and matching records from departments tables whose salary is smaller than 5000.

A

Select e.employee_ID, e.First_name, e.Manager_ID, e.Salary, e.Department_ID, d.Department_name
From Employees e Left outer Join departments d
On e.department_ID = d.department_ID
Where salary < 5000;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Query to display Employee_id, First_name, Manager_id, Salary, Department_id, Department_name, matching records from employees tables and all records from departments tables whose salary is smaller than 5000.

A

Select e.employee_ID, e.First_name, e.Manager_ID, e.Salary, e.Department_ID, d.Department_Name From employees e right outer join department d
On e.department_ID = d.department_ID
Where Salary < 5000;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Query to display Employee_id, First_name, Salary, Department_id, Department_name, only matching records from employees and departments tables whose salary is greater than 10000

A

Select e.Employee_id, e.First_name, e.Salary, e.department_id, d.department_name
From Employees e Inner Join Departments d
On e .department_id = d. department_id
Where Salary >10000;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Query to display Employee_id, First_name, Job_id, Salary, Department_id, Department_name, all matching non-matching records from employees and departments tables whose job_id is ‘ST_CLERK’.

A

Select e.Employee_id, e.First_name, e.Job_id, e.Salary, e.department_id, d.department_name
From Employees e Full outer join Departments d
On e .department_id = d. department_id
Where Job_id = ‘ST_CLERK’ ;

23
Q

What is Aggregate function and the subgroups of it ?

A

It is the function where the values of multiple rows are grouped together as input and ends up forming a single value

  1. COunt()
  2. SUm()
  3. Avg()
  4. Min()
  5. Max(0
24
Q

Write a query to find out total sum of salary who is working in Department_ID = 10

A

Select Sum( Salary ) from employees where department_ID = 10;

25
Q

Write a query to find out the average salary of all employees for each departments

A

Select department_ID AVG(salary)
from employees
Group by department_ID;

26
Q

Write a query to find out the MIN salary, max salary, average salary, sum salary of each departments from employees

A

Select department_ID, min(salary), max(salary), avg(salary), sum(salary)
from employees
Group by department_ID;

27
Q

Write a query to display employee_ID, first_name, last_name, salary, job_ID from employees table with the revised salary
job_ID : marketing then 2salary
job_ID: IT_prog then 3salary
job_ID: human_resource then 4salary
Default: 1.5salary

A

Select employee_ID, first_name, last_name, salary, job_ID, Decode( job_ID, ‘marketing’, 2salary, ‘IT_PROG’, 3salary, ‘human_resource’, 4salary, 1.5salary)
Revised Salary from employees;

28
Q

What is the Case statement and what is the syntax ?

A

SQL way of handing if/then logic

Syntax:
When condition_1 THEN ‘result_1’
When condition_2 THEN ‘result_2’
…..
When condition_n THEN ‘result_n’
ELSE result
END

29
Q

Write a query to display employee_ID, first_name, last_name, salary, salary_type where the conditions are: If salary is less than or equal to 5000 it should display as min_sal
if salary > 5000 and less than 10000 it should display as mid_sal
Else high sal

A

Select employee_ID, first_name, last_name, salary,
CASE WHEN salary <= 5000 THEN ‘min_sal’
When Salary > 5000 AND Salary < 10000 THEN ‘mid_sal’
ELSE ‘High Sal’
END Mid_Salary
From employees;

30
Q

Write a query to display average salary, department_ID from employees table group by department_ID where avg(salary) > 5000

A

Select avg(salary), department_ID
From employees
Where salary > 5000
Group by department_ID

31
Q

Write a query to display department-ID, total number of employees, sum of salary from employees table for each department_ID where salary > 6000 group by department_ID has at least 5 employees

A

Select department_ID, count(employees), sum(Salary)
From employees
Where salary > 6000
Group by Department_ID HAVING COUNT(Employee_ID)>=5;

32
Q

What is Coalesce ? What is the syntax ?

A

A function that returns the first non null expression among its arguments. Basically if you see more than one null. We use Coalesce when we need to replace a null with another value.

Properties of SQL Coalesce function

  • Expressions must be the same data type
  • It can contain multiple expressions
  • This is a syntactic shortcut for the case expression
  • Always evaluates for an integer first, an integer followed by an character expression yields integer as an output

Syntax– Coalesce( Expression 1, Expression 2, …….. Expression)
From table_name;

33
Q

Query that display FIRST_NAME, MANAGER_ID, COMMISSION_PCT, if MANAGER_ID is null, function show COMMISSION_PCT value, If COMISSION_PCT is null than it returns MANAGER_ID value

A

Select FIRST_NAME, MANAGER_ID, COMMISSION_PCT, COALESCE(MANAGER_ID, COMMISSION_PCT) from employees;

34
Q

What is NULLIF? What is the syntax ?

A

It is a function that compares two values typically expressions or columns and it compares those expressions. If the expressions are equal, Nullif returns null. If the expressions are not equal it will return the first value

Nullif(expression1, expression2)

35
Q

Write a query to display all records of the employee from employees table who got a minimum salary

A

Select * employees where salary = (Select min(salary) from employees));

36
Q

Write a query to find name of employees who are working on a project

A

Select name from employees where EMP_ID IN(Select( EMP_ID from projects)

37
Q

Write a query to display employees data who are working in ‘sales’ department_name

A

Select * from employees where DID in (Select DID from departments where department_Name = ‘Sales’));

38
Q

Write a query to fetch employee_ID, First_Name, Last_name, Salary, from employees table whose salary is equal than salary of employee_ID 110

A

Select employee_ID, first_name, last_name, salary from employees where salary = (select salary from employees where employee_ID = 110));

39
Q

What is the main difference between decode and case ?

A

with Decode

  1. Decode is a function
  2. It is not used in the where clause
  3. It is used in the sql
  4. It works with quality (=) operator
  5. Decode execution process is slow in the process

With Case

  1. Case is a statement(extended version of decode)
  2. It is used in the where clause
  3. It is used on both SQL & PL/SQL
  4. Case work with all relational operator (<,>,<=,>=,!=) as well as =
  5. Case executes fast
40
Q

What is NVL ?

What is the syntax ?

A

Stands for null value. The function that replaces null value with another value

Syntax: NVL( String, replace_with)

41
Q

Write a query to check if value of first_name column is null then it print the value of last_name column from employees table

A

Select nvl(first_name, last_name) from employees;

42
Q

What is NVL2 ?

A

It is the extension of NVL.
The oracle NVL2() function accepts three arguments.
If the first argument is not null, then it returns the 2nd argument.
In case the second argument is null, then it returns the third argument.

43
Q

Ques: Display First_name, Salary, Commission_pot and print sum of salary and COMMISSION_PCT when COMMISSION_PCT has value otherwise print only SALARY with header as SAL from EMPLOYEES.

A

Queries: SELECT First name, Salary, Commission_pct, NVL2(COMMISSION_PCT, SALARY + COMMISSION_PCT, SALARY) as SAL from EMPLOYEES;

44
Q

What is Subquery ?

What are the muliple bullet points ?

A

A subquery is a query within another query. The outer query is called as main query and inner query is called as subquery.

  • The subquery generally executes first, and its output is used to complete the query condition for the main or outer query.
  • Subquery must be enclosed in parentheses.
  • Subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.
  • Subqueries can be used with 4 statements along with expression operator.
  • It could be equality or comparison operator such as =, >, =, <= and Like operator.
45
Q

What are the 4 ways subquery can be used ?

A
  1. Select
  2. Insert
  3. Update
  4. Delete
46
Q

What is the syntax for Subquery Select method ?

A

SELECT column__name [, column_name ]


FROM table1 [, table2 ]


WHERE column_name OPERATOR

(SELECT column_name [, column_name ]FROM table1 [, table2 ][WHERE])

47
Q

What is the syntax for INSERTING for Subquery ?

A

INSERT INTO table_name [ (column1 [, column2
SELECT [ *|column1 [, column2 ]

FROM table1 [, table2 ]

[ WHERE VALUE OPERATOR ]

48
Q

What is the syntax for Update Subquery ?

A

UPDATE table

SET column_name = new_value

[ WHERE OPERATOR [ VALUE ]

(SELECT COLUMN_NAME
 FROM TABLE_NAME)
 [ WHERE) ]

49
Q

What is the Delete syntax for Subquery ?

A

DELETE FROM TABLE_NAME

[ WHERE OPERATOR [ VALUE ]

(SELECT COLUMN_NAME
 FROM TABLE_NAME)
 [ WHERE) ]

50
Q

Question: Display Employee_id, First_name, Salary from employees where employee_id is equal to those employees whose salaryis greater than 4500.

A

QUERY: Select Employee_id, First_name, Salary

From Employees
where Employee_id IN
 (Select employee_id from Employees where Salary>4500);

51
Q

Ques:- Copy the complete EMPLOYEES table into the EMPLOYEES_NEW.

A

INSERT INTO EMPLOYEES_NEW
 SELECT * FROM EMPLOYEES

WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM EMPLOYEES) ;

52
Q

Ques:- Query to update the SALARY by 0.25 times into the EMPLOYEES table for the EMPLOYEES whose JOB_IT = ‘IT_PROG’.

A

Query:- UPDATE EMPLOYEES

SET SALARY = SALARY * 0.25

WHERE JOB_ID IN
(SELECT JOB_ID FROM EMPLOYEES_NEW
 WHERE JOB_ID = ‘IT_PROG’ );

53
Q

Ques :- The query deletes the records of the table EMPLOYEES whose Manager_ID is greater than 130.

A

Query :- DELETE FROM EMPLOYEES
 WHERE MANAGER_ID IN (SELECT MANAGER_ID FROM EMPLOYEES_NEW WHERE MANAGER_ID > 130 );

Review for Data Engineer (5 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions