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.
sp_CONFIGURE show advanced, 0 GO RECONFIGURE GO sp_CONFIGURE GO
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:
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.
5 What are the types of views in SQL?
In SQL, the views are classified into four types. They are:
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.
Syntax:
Example:
Output:
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.
Syntax:
Output:
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.