資料庫sql語句常見面試題_常見SQL面試問題：您的資料庫備忘單

資料庫sql語句常見面試題

The following are some of the most commonly-asked SQL questions in job interviews.

以下是工作面試中最常提出的一些SQL問題。

By understanding these, you will be better-prepared for your upcoming technical interviews.

通過了解這些内容，您将為即将進行的技術面試做好更好的準備。

什麼是SQL中的内部聯接？ (What is an inner join in SQL?)

This is the default type of join if no join is specified. It returns all rows in which there is at least one match in both tables.

如果未指定連接配接，則這是預設的連接配接類型。 它傳回兩個表中至少有一個比對項的所有行。

SELECT * FROM A x JOIN B y ON y.aId = x.Id
           

什麼是SQL中的左聯接？ (What is a left join in SQL?)

A left join returns all rows from the left table, and the matched rows from the right table. Rows in the left table will be returned even if there was no match in the right table. The rows from the left table with no match in the right table will have  

null

 for right table values.

左聯接傳回左表中的所有行，并傳回右表中的比對行。 即使右表中沒有比對項，也将傳回左表中的行。 左表中沒有比對項的行在右表中将為

null

對于右表值。

SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id
           

什麼是SQL中的正确聯接？ (What is a right join in SQL?)

A right join returns all rows from the right table, and the matched rows from the left table. Opposite of a left join, this will return all rows from the right table even where there is no match in the left table. Rows in the right table that have no match in the left table will have  

null

 values for left table columns.

右聯接傳回右表中的所有行，以及左表中的比對行。 與左聯接相反，這将傳回右表中的所有行，即使左表中沒有比對項也是如此。 右表中與左表不比對的行的左表列将具有

null

值。

SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id
           

什麼是SQL中的完全聯接？ (What is a full join in SQL?)

A full join returns all rows for which there is a match in either of the tables. So if there are rows in the left table that do not have matches in the right table, those will be included. As well as if there are rows in the right table that do not have matches in the left table, those will be included.

完全聯接傳回在兩個表中都比對的所有行。 是以，如果左表中的行與右表中的行不比對，則将這些行包括在内。 以及如果右表中的行與左表中的行不比對，這些行也将包括在内。

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName
           

以下指令的結果是什麼？ (What is the result of the following command?)

DROP VIEW view_name
           

Here it’ll be an error because we can’t perform a DML operation on a view.

這将是一個錯誤，因為我們無法在視圖上執行DML操作。

使用ALTER指令後可以執行復原嗎？ (Can we perform a rollback after using ALTER command?)

No, because ALTER is a DDL command and Oracle server performs an automatic COMMIT when the DDL statements are executed.

不可以，因為ALTER是DDL指令，并且在執行DDL語句時Oracle伺服器執行自動COMMIT。

在列級别執行規則的唯一限制是什麼？ (Which is the only constraint that enforces rules at column level?)

NOT NULL is the only constraint that works at the column level.

NOT NULL是在列級别上起作用的唯一限制。

SQL中的僞列是什麼？ 舉一些例子？ (What are the pseudocolumns in SQL? Give some examples?)

A pseudocolumn is a function which returns a system generated value. The reason it is known as so because a pseudocolumn is an Oracle assigned value used in the same context as an Oracle database column but not stored on disk.

僞列是一個傳回系統生成值的函數。 之是以這樣說是因為僞列是在與Oracle資料庫列相同的上下文中使用但未存儲在磁盤上的Oracle配置設定值。

ROWNUM, ROWID, USER, CURRVAL, NEXTVAL etc.
           

建立一個密碼為kmd26pt的使用者my723acct。 使用PO8提供的使用者資料和臨時資料表空間，并向該使用者提供10M的使用者資料存儲空間和5M的臨時資料存儲空間。 (Create a user my723acct with password kmd26pt. Use the user data and temporary data tablespaces provided by PO8 and provide to this user 10M of storage space in user data and 5M of storage space in temporary_data.)

CREATE USER my723acct IDENTIFIED BY kmd26pt
    DEFAULT TABLESPACE user_data
    TEMPORARY TABLESPACE temporary_data
    QUOTA 10M on user_data QUOTA 5M on temporary_data
           

建立角色角色表 and_views。 (Create the role role tables and_views.)

CREATE ROLE role_tables_and_views
           

向上一個問題的角色授予連接配接資料庫的特權以及建立表和視圖的特權。 (Grant to the role of the previous question the privileges to connect to the database and the privileges to create tables and views.)

The privilege to connect to the database is CREATE SESSION The privilege to create table is CREATE TABLE The privilege to create view is CREATE VIEW

連接配接資料庫的特權是CREATE SESSION建立表的特權是CREATE TABLE建立視圖的特權是CREATE VIEW

GRANT Create session, create table, create view TO role_tables_and_views
           

将問題中的先前角色授予使用者anny和rita (Grant the previous role in the question to the users anny and rita)

GRANT role_tables_and_views TO anny, rita
           

建立一個密碼為kmd26pt的使用者my723acct。 使用PO8提供的使用者資料和臨時資料表空間，并向該使用者提供10M的使用者資料存儲空間和5M的臨時資料存儲空間。 (Create a user my723acct with password kmd26pt. Use the user data and temporary data tablespaces provided by PO8 and provide to this user 10M of storage space in user data and 5M of storage space in temporary_data.)

CREATE USER my723acct IDENTIFIED BY kmd26pt
    DEFAULT TABLESPACE user_data
    TEMPORARY TABLESPACE temporary_data
    QUOTA 10M on user_data QUOTA 5M on temporary_data
           

建立角色角色表 and_views。 (Create the role role tables and_views.)

CREATE ROLE role_tables_and_views
           

向上一個問題的角色授予連接配接資料庫的特權以及建立表和視圖的特權。 (Grant to the role of the previous question the privileges to connect to the database and the privileges to create tables and views.)

The privilege to connect to the database is CREATE SESSION The privilege to create table is CREATE TABLE The privilege to create view is CREATE VIEW

連接配接資料庫的特權是CREATE SESSION建立表的特權是CREATE TABLE建立視圖的特權是CREATE VIEW

GRANT Create session, create table, create view TO role_tables_and_views
           

将問題中的先前角色授予使用者anny和rita。 (Grant the previous role in the question to the users anny and rita.)

GRANT role_tables_and_views TO anny, rita
           

編寫指令以将使用者rita的密碼從abcd更改為dfgh。 (Write a command to change the password of the user rita from abcd to dfgh.)

ALTER USER rita IDENTIFIED BY dfgh
           

The users rita and anny do not have SELECT privileges on the table INVENTORY that was created by SCOTT. Write a command to allow SCOTT to grant the users SELECT priviliges on these tables.

使用者rita和anny對由SCOTT建立的INVENTORY表沒有SELECT特權。 編寫指令以允許SCOTT授予使用者對這些表的SELECT特權。

GRANT select ON inventory TO rita, anny
           

User rita has been transferred and no longer needs the privilege that was granted to her through the role role tables and_views. Write a command to remove her from her previous given privileges except that she still could connect to the database.

使用者rita已轉移，不再需要通過角色角色表and_views授予她的特權。 編寫指令以從她先前的特權中删除她，但她仍然可以連接配接到資料庫。

REVOKE select ON scott.inventory FROM rita
    REVOKE create table, create view FROM rita
           

The user rita who was transferred is now moving to another company. Since the objects that she created is of no longer use, write a commmand to remove this user and all her objects.

已轉移的使用者rita現在移至另一家公司。 由于她建立的對象已不再使用，是以請寫一個指令删除該使用者及其所有對象。

Here CASCADE option is necessary to remove all the objects of the user in the database.

在這裡，CASCADE選項對于删除資料庫中使用者的所有對象都是必需的。

DROP USER rita CASCADE

### User rita has been transferred and no longer needs the privilege that was granted to her through the role role_tables_and_views. Write a command to remove her from her previous given priviliges except that she still could connect to the database.
``` sql    
    REVOKE select ON scott.inventory FROM rita
    REVOKE create table, create view FROM rita
           

The user rita who was transferred is now moving to another company. Since the objects that she created is of no longer use, write a commmand to remove this user and all her objects.

已轉移的使用者rita現在移至另一家公司。 由于她建立的對象已不再使用，是以請寫一個指令删除該使用者及其所有對象。

Here CASCADE option is necessary to remove all the objects of the user in the database.

在這裡，CASCADE選項對于删除資料庫中使用者的所有對象都是必需的。

DROP USER rita CASCADE
           

編寫SQL查詢以從表中找到第n個最高薪水。 (Write SQL query to find the nth highest salary from table.)

SELECT TOP 1 Salary
   FROM (
      SELECT DISTINCT TOP N Salary
      FROM Employee
      ORDER BY Salary DESC
      )
    ORDER BY Salary ASC
           

翻譯自: https://www.freecodecamp.org/news/sql-interview-questions/

資料庫sql語句常見面試題