数据库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语句常见面试题