[SQL interview question 2] MySQL and Oracle Database

Time:2020-7-23
1. If matched, update desc of dictb to corresponding field desc in dicta

[SQL interview question 2] MySQL and Oracle Database

First create two tables:

CREATE TABLE DictA(id1 number, desc1 varchar2(10));
		CREATE TABLE DictB(id1 number, desc1 varchar2(10));
		Insert into dicta values (1001, 'fund');
		Insert into dicta values (2001, 'insurance');
		Insert into dicta values (3001, 'securities');
		Insert into dicta values (3002, 'trust');
		Insert into dictb values (1002, 'securities');
		Insert into dictb values (3001, 'bank');
COMMIT;

Solution:
If the ID matches, it will be modified (inserted if the ID cannot match), which is similar to incremental synchronization;
You can also use ID and desc to combine judgment.

MERGE INTO DICTB B
		USING (SELECT * FROM DICTA) A
		ON (B.ID1 = A.ID1)
		WHEN MATCHED THEN 
UPDATE SET B.DESC1 = A.DESC1; 

matters needing attention:

--Ora.00903 table name is invalid, source table should be enclosed with () brackets
--Ora.00969 is missing the on keyword, and the on condition should also be enclosed with () brackets

2. As shown in the figure on the right, the pass score is 60;

[SQL interview question 2] MySQL and Oracle Database

First create the table:

CREATE TABLE COURSE(COURSEID NUMBER,COUSERNAME VARCHAR2(10),SCORE NUMBER);
			INSERT INTO COURSE VALUES (1,  'java',70);
			INSERT INTO COURSE VALUES (2,  'Servlet',60);
			INSERT INTO COURSE VALUES (3,  'Oracle',90);
			INSERT INTO COURSE VALUES (4,  'xml',40);
			INSERT INTO COURSE VALUES (5,  'jsp',50);
			INSERT INTO COURSE VALUES (6,  'Chinese',80);
	COMMIT;

Solution:
Case when judges the score by multiple conditions and adds a new column to rename mark.

SELECT C.*,
			CASE WHEN SCORE < 60 THEN 'FAIL'
			ELSE 'PASS' END AS MARK
	FROM COURSE; 

matters needing attention:

Ora.00923 did not find the required from keyword - cannot return all sub segments directly with *
			The database is unrecognized after inserting a new column mark.
	So alias the table and then use it*

3. Transfer all the girls in the table to table B.

[SQL interview question 2] MySQL and Oracle Database

First create the table:

CREATE TABLE T_CLASSA(NAME VARCHAR2(10),GENDER VARCHAR2(2),STUNUM VARCHAR2(10));
				insert into  T_ Class values ('li Zhen ','male','c20160001 ');
				insert into  T_ Class values ('zhang Yu? ','male','c20160002 ');
				insert into  T_ ClassA values ('wang forest ',' male ',' c20160003 ');
				insert into  T_ Class values ('Du Pei','male ','c20160004');
				insert into  T_ Class values ('liu Fuqi ',' male ',' c20160005 ');
				insert into  T_ Class values ('qin Yu ','male','c20160007 ');
				insert into  T_ Class values ('Feng Yu','male ','c20160009');
				insert into  T_ ClassA values ('Nie Zhifu', 'male', 'c20160011');
				insert into  T_ Class values ('Golden rafter ','male','c20160013 ');
				insert into  T_ Class values ('Hu Minjing', 'male', 'c20160014');
		insert into  T_ Class values ('li Lei ',' female ',' c20160015 ');

Analysis ideas:
Insert a temporary table and filter out the gender in the source table to be equal to ‘female’

INSERT INTO T_CLASSB SELECT * FROM T_CLASSA WHERE GENDER ='female';

For specific links, please refer to:[pl / SQL] – backup table idea create table and insert into


4. Convert the following format to the right figure D_ Day shows:

[SQL interview question 2] MySQL and Oracle Database

Select To_Date(c_Day,'yyyy/mm/dd') As d_Day From Tmonth;

5. Note that the data that cannot be matched will be converted to unknown.

[SQL interview question 2] MySQL and Oracle Database

Create two tables:

CREATE TABLE T_COURSE
						(
						SID VARCHAR(4),
						CourseID NUMBER(2)
						);

						CREATE TABLE T_NAME
						(
						COURSEID NUMBER(4),
						CNAME VARCHAR2(4)
						);

						INSERT INTO T_COURSE VALUES('1001',1);
						INSERT INTO T_COURSE VALUES('1001',2);
						INSERT INTO T_COURSE VALUES('1001',3);
						INSERT INTO T_COURSE VALUES('1001',4);
						INSERT INTO T_COURSE VALUES('1002',1);
						INSERT INTO T_COURSE VALUES('1002',2);
						INSERT INTO T_COURSE VALUES('1002',3);

						INSERT INTO T_ Name values (1, 'physics');
						INSERT INTO T_ Name values (2, 'biology');
						INSERT INTO T_ Name values (3, 'chemistry');

						SELECT * FROM T_COURSE;
						SELECT * FROM T_NAME;

Analysis ideas:
1. Sid and course come from two different tables, and the table where sid is located is the main table. Therefore, the left join is used.
2. If there is no corresponding courseid = 4 value on the timetable after the left connection, a null value will appear. Use the null value to convert the number of rows NVL to unknown.
3. The null value attribute does not participate in the aggregation logic operation, and the sorting is the largest. Finally, don’t forget to add order by to sort the SID course number in ascending order.

SELECT T1.SID,NVL(T2.CNAME,'unknow') FROM  T_COURSE T1
						LEFT JOIN T_NAME T2
						ON T1.COURSEID = T2.COURSEID
				ORDER BY T1.SID;

6. Query the last call record

[SQL interview question 2] MySQL and Oracle Database

To create a table:

CREATE TABLE T_CONTACT
							(
							Date VARCHAR2 (10),
							Name VARCHAR2 (4),
							Call log number (10)
							);

							INSERT INTO T_ Contact values ('2015 / 07 / 23 ','zhang San', 11111111);
							INSERT INTO T_ Contact values ('2015 / 07 / 24 ','zhang San', 22222222);
							INSERT INTO T_ Contact values ('2015 / 07 / 25 ','zhang San', 33333333);
							INSERT INTO T_ Contact values ('2015 / 07 / 23 ','li Si', 11111111);
							INSERT INTO T_ Contact values ('2015 / 07 / 24 ','li Si', 22222222);
							INSERT INTO T_ Contact values ('2015 / 07 / 25 ','li Si', 33333333);
							INSERT INTO T_ Contact values ('2015 / 07 / 24 ','wangwu', 22222222);
					INSERT INTO T_ Contact values ('2015 / 07 / 25 ','wangwu', 11111111);

Analysis ideas:
See that the target table has only one date field, subdivided into daily.
There are two methods: one is to sort by analysis function and take the first place of each date.
The maximum value of the date is judged by max.

---Method 1: using analysis function
							SELECT * FROM 
							(
							SELECT T.*, 
							ROW_ Number() over (partition by name order by date) as RK
							FROM T_CONTACT T
							) WHERE RK =1;

							---Method 2: apply the subquery and take the maximum value
							SELECT * FROM T_ Contact where date in
							(
							Select max from t_ Contact group by name
					);

7. Find the duplicate name

[SQL interview question 2] MySQL and Oracle Database

Analysis ideas:
Group by name. If the value after grouping is greater than 1, it means repetition;

Select name from emp group by name having count(1)>1;
						

8. Find employee names without j

[SQL interview question 2] MySQL and Oracle Database

Analysis ideas:
1. Use instr to make fuzzy query, and then make judgment
2. Don’t forget the judgment of null,

--Fuzzy query like
									Select name from emp\_test where name 
									not like '%J%' or name is null;  

									--Instr query string, does the character exist?
									Select name from emp\_test WHERE 
							INSTR(NAME,'J',1,1)=0 or name is null;

9. Check the number of people in this class.

The highest score, lowest score and average score of each group. And according to the average score descending order.

[SQL interview question 2] MySQL and Oracle Database

Analysis ideas:
1. Count the number of students in each class, and group by class is the first step
2. The highest, lowest and average scores are aggregate functions, which are usually used in combination with group by
3. Order by desc
4. Create a new column for each new indicator

SELECT C_ Class, count (1) as number, max (score) as highest score, min (score) as lowest score, AVG (score) as average score from TScore
										GROUP BY C_CLASS
								The average score of order by was desc;

10. Create views, calculate month on month, year-on-year

[SQL interview question 2] MySQL and Oracle Database

Analysis ideas:
1. Create or replace view view_ name AS ();
2. Compared with the same period of last year, the displacement analysis function is used

CREATE OR REPLACE VIEW v_sale AS
											SELECT MONTHS,sell,
											Lag (sell, 12) over (order by months) as lastyear, -
											Lag (sell, 1) over (order by months) as lastmonth
									FROM Sale;

11. The left figure is used as the source table to generate the right figure

[SQL interview question 2] MySQL and Oracle Database

Analysis ideas:
1. Sell sales are classified and summarized according to the “reason” field
2. The session field consists of several strings, concat or |
3.To_ Char function range quarter

To create a table:

CREATE TABLE T_SEASON
												(
												MONTH VARCHAR2(10),
												SELL NUMBER(4)
												);

												INSERT INTO T_SEASON VALUES('201501',1000);
												INSERT INTO T_SEASON VALUES('201502',1100);
												INSERT INTO T_SEASON VALUES('201503',1300);
												INSERT INTO T_SEASON VALUES('201504',1000);
												INSERT INTO T_SEASON VALUES('201505',1000);
												INSERT INTO T_SEASON VALUES('201506',1000);
												INSERT INTO T_SEASON VALUES('201507',1000);
												INSERT INTO T_SEASON VALUES('201508',4000);
												INSERT INTO T_SEASON VALUES('201509',1000);
												INSERT INTO T_SEASON VALUES('201510',3000);
												INSERT INTO T_SEASON VALUES('201511',1000);
												INSERT INTO T_SEASON VALUES('201512',2200);
												INSERT INTO T_SEASON VALUES('201601',1700);

										SELECT * FROM T_SEASON

The answers are as follows:

SELECT SUBSTR(T.MONTH,1,4)||'Q'||TO_CHAR(TO_DATE(T.MONTH,'yyyy/mm'),'Q') AS SEASON,SUM(SELL) FROM
												T_SEASON T
										GROUP BY SUBSTR(T.MONTH,1,4)||'Q'||TO_CHAR(TO_DATE(T.MONTH,'yyyy/mm'),'Q')

matters needing attention:

1. Substr function (character, from bit to bit, intercept length)
												2. If there is a common character connection, it can be considered to use |;
												3. The character must be converted to date mode before using to_ The second parameter Q of char returns the current quarter.
										4. Group by cannot add group function (aggregate)

[SQL interview question 2] MySQL and Oracle Database


12. Update as shown in the right figure

[SQL interview question 2] MySQL and Oracle Database

Analysis ideas:
1. The name of the table is the same, and it is operated on the same table. Only the name field is updated. Use the update set statement.
2. Name is equal to firstname plus space plus LastName.

Update ClassaA Set Name=Firstname||' '||lastname;

13. As shown in the figure below, add a column to get the result in the right figure

[SQL interview question 2] MySQL and Oracle Database

Analysis ideas:
1. Using case when to judge age multi condition
2. The logical structure is like question 2

Select Id,Name,Age,
														(Case When Age<=20 Then '20-'
														When Age<= 30 Then '21 to 30'
														When Age<= 40 Then '31 to 40'
														When Age<= 50 Then '41 to 50'
														Else '50+' End) AS AgePara
												From Age;

14. Similar to data cleaning, the space in the name is replaced and removed directly.

[SQL interview question 2] MySQL and Oracle Database

Analysis ideas:
1. Replace the function with replace and replace the space with ”.

Select Id,Replace(Name,' ','') As Name From Name;

15,Using the SQL statement, you are going to create a function F_MAX(PI_VAR1 IN NUMBER, PI_VAR2 IN NUMBER), with 2 input parameter which are PI_VAR1 and PI_VAR2. If the value of PI_VAR1 is larger than PI_VAR2, the value of PI_VAR1 is returned, otherwise the value of PI_VAR2 is returned

Write a custom function to input two numbers and judge.

First, we need to write a custom function model.

CREATE OR REPLACE FUNCTION FUNCTION_NAME(PI_VAR1 IN NUMBER, 
																PI_VAR2 IN NUMBER)
																Return number -- the return type of the function
																IS
																BEGIN
																--Judge the two values, who beat the smaller one
														END;

Answer:

CREATE OR REPLACE FUNCTION F_MAX(PI_VAR1 IN NUMBER, 
																PI_VAR2 IN NUMBER)
																Return number -- the return type of the function
																IS
																BEGIN
																IF PI_VAR1>PI_VAR2 THEN
																RETURN PI_VAR1;
																ELSE
																RETURN PI_VAR2;
																END IF;
														END;