Adventure Works Interview Questions

What Command do we Use to Rename a db, a Table and a Column?

If someone is using db it will not accept sp_renmaedb. In that case, first bring db to single user mode using sp_dboptions. Use sp_renamedb to rename the database. Use sp_dboptions to bring the database to multi-user mode.

USE MASTER; GO EXEC sp_dboption AdventureWorks, Single User, True GO EXEC sp_renamedb AdventureWorks, AdventureWorks_New GO EXEC sp_dboption AdventureWorks, Single User, False GO

We can change the table name using sp_rename as follows:

The script for renaming any column is as follows:

sp_RENAME Table_First.Name, NameChange , COLUMN GO

What are sp_configure Commands and SET Commands?

Use sp_configure to display or change server-level settings. To change the database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.


You can run the following command and check the advanced global configuration settings.sp_CONFIGURE show advanced, 1 GO RECONFIGURE GO sp_CONFIGURE GO

6 Describe how to delete duplicate rows using a single statement but without any table creation.

Let us create an employee table where the column names are ID, NAME, DEPARTMENT, and EMAIL. Below are the SQL scripts for generating the sample data:

adventure works interview questions

We can see the duplicate rows in the above table.

The SQL query above will delete the rows, where the name fields are duplicated, and it will retain only those unique rows in which the names are unique and the ID fields are the lowest, i.e., the rows with IDs 5 and 6 are deleted, while the rows with IDs 1 and 2 are retained.

adventure works interview questions

5 What are the types of views in SQL?

In SQL, the views are classified into four types. They are:

  • Simple View: A view that is based on a single table and does not have a GROUP BY clause or other features.
  • Complex View: A view that is built from several tables and includes a GROUP BY clause as well as functions.
  • Inline View: A view that is built on a subquery in the FROM clause, which provides a temporary table and simplifies a complicated query.
  • Materialized View: A view that saves both the definition and the details. It builds data replicas by physically preserving them.
  • Basic SQL Interview Questions

    A database is an organized collection of structured data that can be stored, easily accessed, managed, and retrieved digitally from a remote or local computer system. Databases can be complex and vast and are built with a fixed design and modeling approach. While smaller databases can be stored on a file system, large ones are hosted on computer clusters or cloud storage.

    5 Explain Inner Join with an example.

    Inner Join basically gives us those records that have matching values in two tables.

    Let us suppose that we have two tables, Table A and Table B. When we apply Inner Join on these two tables, we will get only those records that are common to both Table A and Table B.




    adventure works interview questions

    Now, we will apply Inner Join to both these tables, where the e_dept column in the employee table is equal to the d_name column of the department table.



    adventure works interview questions

    After applying Inner Join, we have only those records where the departments match in both tables. As we can see, the matched departments are Support, Analytics, and Sales.

    SQL Union All vs Union Interview Question Adventure Works Example

    Related Posts

    Leave a Reply

    Your email address will not be published. Required fields are marked *