Basic SQL interview Questions - Part 1

This post contain the very basic question of SQL, today onward i will write multiple post with interview questions. So that it will be help you guys the question i am going to post really crispy and tricky. If you would like to share the any interview question with me, comment on any post . I will definitely

share it on my blog. 

1. Difference Between Group by Clause and Having Clause 

2. Different type of Clause in SQL Query

3. I have below table and some sample data in it, create a query to show the department name and total number employees working in department.

ID	DepartmentName	NumberOfEmployees
1	Dept A	140
2	Dept A	100
3	Dept B	300
4	Dept B	400
5	Dept C	400
6	Dept C	700

Once you will be able to find the Department Name and Its total number of employees, then write query to populate the records for condition, "Whose number of employee count more than 700"

4. What is left join how it will work, write query of left join and what will be output, for below sample tables/data. (Below table are refer using TabA.CountryID = TabB.ID 

Tab-A

ID	Name	CountryID
1	A	21
2	B	31
3	C	31
4	D	11
5	E	11
6	F	31

Tab-B

ID	CountryName
11	India
21	United Status
31	Singapore

2- question - Find the total number of employee living in each country, Write down the syntax and query output.

SQLServer Function Replace Special Char

I have 1 field which contains special characters in the data, and i want to remove special character and need to keep on A to Z, a to z, 0-9 and few special character allowed. I tried to use simple SQL query so that i can update column using update query using regular expression. But i noticed this is not working in replace function.

Regular expression worked only in WHERE clause to filter the records. So i have only way to create function to remove special characters from the string.

Create below function in database and use it to remove special character from string. Feel free to modify this as per your requirement.

CREATE FUNCTION ReplaceInvalidChar (@In_String VARCHAR(2000))
RETURNS VARCHAR(2000)
BEGIN
    DECLARE @temp VARCHAR(2000) = @In_String;
    DECLARE @List_Of_Invalid_Char VARCHAR (40) = '%[^0-9a-zA-Z_.#]%';
    DECLARE @In_String_Len INT;
    SELECT @In_String_Len = LEN(@In_String); 
    WHILE @In_String_Len > 0 
    BEGIN
        SET @In_String_Len = @In_String_Len - 1;
        IF (PATINDEX(@List_Of_Invalid_Char,@temp) > 0)
            BEGIN
                SELECT @temp = STUFF(@temp, PATINDEX(@List_Of_Invalid_Char,@temp),1,'');    
            END
        ELSE
        BEGIN
            BREAK;
        END
    END     
    RETURN @temp
END

SYS.DBMS_DATAPUMP' must be declared

impdp DIRECTORY=DIR DUMPFILE=dump_file.DMP LOGFILE=dump_file_log.log PARALLEL=8 EXCLUDE=INDEX remap_schema=Old_User:New_User remap_tablespace=Old_Tablespace:New_Tablespace exclude=statistics 

SYS.DBMS_DATAPUMP' must be declared

When This Error Came:

I tried to restore the Oracle Database and it throws the error. I Used correct credentials to connect oracle database.

My machine has multiple oracle Instances installed.

Both Instances are installed on separate Path.

Solution:

To solve this error i executed below command on CMD window. (make sure you are not entered in SQLPLUS)

SET ORACLE_SID=ORCL

using this command we need to tell the machine, which instance we are plan to use, if you wan to switch your your oracle instance re-run same query after exiting SQLPLUS cmd.

Database '*********Database Name*******' is already open and can only have one user at a time

When i was trying to restore database on SQL Server, after some time database restore got failed cause of insufficient space in drive. When i changed my mind and decide not to restore database. I click on "New Query" button to start SQL query window. what i observed it got connect to master database instead of my database on which i want to write SQL query.

Second thing i observed, my database entered in single user mode, it appear on left pane window as "Database Name (Single User)"

How to Fix this:

Execute below query after connecting to SQL Server on any database 

ALTER DATABASE DatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Your problem will get resolved and you will be able to execute query on your database.