Sql Training - 15

 



Question 3: Write SQL Query to display the current date?
Answer: SQL has built-in function called GetDate() which returns the current timestamp. This will work in Microsoft SQL Server, other vendors like Oracle and MySQL also have equivalent functions.
SELECT GetDate(); 


Question 4: Write an SQL Query to check whether the date passed to Query is the date of the given format or not?
Answer: SQL has IsDate() function which is used to check passed value is a date or not of specified format, it returns 1(true) or 0(false) accordingly. Remember the ISDATE() is an MSSQL function and it may not work on Oracle, MySQL, or any other database but there would be something similar.

SELECT  ISDATE('1/08/13') AS "MM/DD/YY"; 

Question 5: Write an SQL Query to print the name of the distinct employee
SELECT DISTINCT EmpName 
FROM Employees

Option 1: Change SQL Server Password in Management Studio

If you've forgotten the sa password for your SQL server, you are probably in a panic. Fear not, for you can change the password and once again have access to the powers of the SA account using Windows Authentication mode.

  1. Login into SQL Server using Windows Authentication.

  2. In Object Explorer, open Security folder, open Logins folder. Right click on SA account and go to Properties.

    SQL Server Management

  3. Change SA password, and confirm it. Click OK.

    Change SQL Server Password

-----------------------------------------

Triggers in SQL Server

A trigger is a set of SQL statements that reside in system memory with unique names. It is a specialized category of stored procedure that is called automatically when a database server event occurs. Each trigger is always associated with a table.

A trigger is called a special procedure because it cannot be called directly like a stored procedure. The key distinction between the trigger and procedure is that a trigger is called automatically when a data modification event occurs against a table. A stored procedure, on the other hand, must be invoked directly.

The following are the main characteristics that distinguish triggers from stored procedures:

  • We cannot manually execute/invoked triggers.
  • Triggers have no chance of receiving parameters.
  • A transaction cannot be committed or rolled back inside a trigger.

Syntax of Trigger

We can create a trigger in SQL Server by using the CREATE TRIGGER statement as follows:

  1. CREATE TRIGGER schema.trigger_name  
  2. ON table_name  
  3. AFTER  {INSERT, UPDATE, DELETE}  
  4. [NOT FOR REPLICATION]  
  5. AS  
  6. {SQL_Statements}  

The parameter descriptions of this syntax illustrate below:

schema: It is an optional parameter that defines which schema the new trigger belongs to.

trigger_name: It is a required parameter that defines the name for the new trigger.

table_name: It is a required parameter that defines the table name to which the trigger applies. Next to the table name, we need to write the AFTER clause where any events like INSERT, UPDATE, or DELETE could be listed.

NOT FOR REPLICATION: This option tells that SQL Server does not execute the trigger when data is modified as part of a replication process.

SQL_Statements: It contains one or more SQL statements that are used to perform actions in response to an event that occurs.

When we use triggers?

Triggers will be helpful when we need to execute some events automatically on certain desirable scenarios. For example, we have a constantly changing table and need to know the occurrences of changes and when these changes happen. If the primary table made any changes in such scenarios, we could create a trigger to insert the desired data into a separate table.

Example of Trigger in SQL Server

Let us understand how we can work with triggers in the SQL Server. We can do this by first creating a table named 'Employee' using the below statements:

  1. CREATE TABLE Employee  
  2. (  
  3.   Id INT PRIMARY KEY,  
  4.   Name VARCHAR(45),  
  5.   Salary INT,  
  6.   Gender VARCHAR(12),  
  7.   DepartmentId INT  
  8. )  

Next, we will insert some record into this table as follows:

  1. INSERT INTO Employee VALUES (1,'Steffan', 82000, 'Male', 3),  
  2. (2,'Amelie', 52000, 'Female', 2),  
  3. (3,'Antonio', 25000, 'male', 1),  
  4. (4,'Marco', 47000, 'Male', 2),  
  5. (5,'Eliana', 46000, 'Female', 3)  

We can verify the insert operation by using the SELECT statement. We will get the below output:

  1. SELECT * FROM Employee;  

Triggers in SQL Server

We will also create another table named 'Employee_Audit_Test' to automatically store transaction records of each operation, such as INSERT, UPDATE, or DELETE on the Employee table:

  1. CREATE TABLE Employee_Audit_Test  
  2. (    
  3. Id int IDENTITY,   
  4. Audit_Action text   
  5. )  

Now, we will create a trigger that stores transaction records of each insert operation on the Employee table into the Employee_Audit_Test table. Here we are going to create the insert trigger using the below statement:

  1. CREATE TRIGGER trInsertEmployee   
  2. ON Employee  
  3. FOR INSERT  
  4. AS  
  5. BEGIN  
  6.   Declare @Id int  
  7.   SELECT @Id = Id from inserted  
  8.   INSERT INTO Employee_Audit_Test  
  9.   VALUES ('New employee with Id = ' + CAST(@Id AS VARCHAR(10)) + ' is added at ' + CAST(Getdate() AS VARCHAR(22)))  
  10. END  

After creating a trigger, we will try to add the following record into the table:

  1. INSERT INTO Employee VALUES (6,'Peter', 62000, 'Male', 3)  

If no error is found, execute the SELECT statement to check the audit records. We will get the output as follows:

Triggers in SQL Server

We are going to create another trigger to store transaction records of each delete operation on the Employee table into the Employee_Audit_Test table. We can create the delete trigger using the below statement:

  1. CREATE TRIGGER trDeleteEmployee   
  2. ON Employee  
  3. FOR DELETE  
  4. AS  
  5. BEGIN  
  6.   Declare @Id int  
  7.   SELECT @Id = Id from deleted  
  8.   INSERT INTO Employee_Audit_Test  
  9.   VALUES ('An existing employee with Id = ' + CAST(@Id AS VARCHAR(10)) + ' is deleted at ' + CAST(Getdate() AS VARCHAR(22)))  
  10. END  

After creating a trigger, we will delete a record from the Employee table:

  1. DELETE FROM Employee WHERE Id = 2;  

If no error is found, it gives the message as below:

Triggers in SQL Server

Finally, execute the SELECT statement to check the audit records:

Triggers in SQL Server

In both the triggers code, you will notice these lines:

  1. SELECT @Id = Id from inserted  
  2. SELECT @Id = Id from deleted  

Here inserted and deleted are special tables used by the SQL Server. The inserted table keeps the copy of the row when you insert a new row into the actual table. And the deleted table keeps the copy of the row you have just deleted from the actual table.Triggers in SQL Server

Example: When we insert data into a table, the trigger associated with the insert operation on that table will fire before the data has passed all constraints, such as the primary key constraint. SQL Server also fires the Instead of Trigger if the data insertion fails.

The following is an illustration of the Instead of Triggers syntax in SQL Server:

  1. CREATE TRIGGER schema_name.trigger_name  
  2. ON table_name  
  3. INSTEAD OF {INSERT | UPDATE | DELETE}  
  4. AS  
  5.    BEGIN  
  6.       -- trigger statements  
  7.       -- Insert, Update, or Delete commands  
  8.    END  

Logon Triggers

Logon triggers are fires in response to a LOGON event. The LOGON event occurs when a user session is generated with an SQL Server instance, which is made after the authentication process of logging is completed but before establishing a user session. As a result, the SQL Server error log will display all messages created by the trigger, including error messages and the PRINT statement messages. If authentication fails, logon triggers do not execute. These triggers may be used to audit and control server sessions, such as tracking login activity or limiting the number of sessions for a particular login.

How to SHOW Triggers in SQL Server?

When we have many databases with multiple tables, the show or list trigger comes in handy. When the table names in multiple databases are the same, this query is extremely helpful. Using the following command, we can see a list of all the triggers available in SQL Server:

  1. SELECT name, is_instead_of_trigger  
  2. FROM sys.triggers    
  3. WHERE type = 'TR';  

If we are using the SQL Server Management Studio, it is very easy to show or list all triggers available in any specific table. We can do this using the following steps:

  • Go to the Databases menu, select desired database, and then expand it.
  • Select the Tables menu and expand it.
  • Select any specific table and expand it.

We will get various options here. When we choose the Triggers option, it displays all the triggers available in this table.

Triggers in SQL Server


Advantages of Triggers

The following are the advantages of using triggers in SQL Server:

  • Triggers set database object rules and roll back if any change does not satisfy those rules. The trigger will inspect the data and make changes if necessary.
  • Triggers help us to enforce data integrity.
  • Triggers help us to validate data before inserted or updated.
  • Triggers help us to keep a log of records.
  • Triggers increase SQL queries' performance because they do not need to compile each time they are executed.
  • Triggers reduce the client-side code that saves time and effort.
  • Triggers are easy to maintain.

Disadvantages of Triggers

The following are the disadvantages of using triggers in SQL Server:

  • Triggers only allow using extended validations.
  • Triggers are invoked automatically, and their execution is invisible to the user. Therefore, it isn't easy to troubleshoot what happens in the database layer.
  • Triggers may increase the overhead of the database server.
  • We can define the same trigger action for multiple user actions such as INSERT and UPDATE in the same CREATE TRIGGER statement.
  • We can create a trigger in the current database only, but it can reference objects outside the current database.

-------------------------------------
CREATE TABLE Employee
    (
    [ID] INT identity(1,1),
    [FirstName] Varchar(100),
    [LastName] Varchar(100),
    [Country] Varchar(100),
    )
    GO
    
    Insert into Employee ([FirstName],[LastName],[Country] )values('Raj','Gupta','India'),
                                ('Raj','Gupta','India'),
                                ('Mohan','Kumar','USA'),
                                ('James','Barry','UK'),
                                ('James','Barry','UK'),
                                ('James','Barry','UK')

---------------------------------------------

SQL delete duplicate Rows using Common Table Expressions (CTE)

We can use Common Table Expressions commonly known as CTE to remove duplicate rows in SQL Server. It is available starting from SQL Server 2005.

We use a SQL ROW_NUMBER function, and it adds a unique sequential row number for the row.

In the following CTE, it partitions the data using the PARTITION BY clause for the [Firstname], [Lastname] and [Country] column and generates a row number for each row.

In the output, if any row has the value of [DuplicateCount] column greater than 1, it shows that it is a duplicate row.

Remove Duplicate Rows using Common Table Expressions (CTE)

We can remove the duplicate rows using the following CTE.


DELETE FROM [Employee]    WHERE ID NOT IN    (        SELECT MAX(ID) AS MaxRecordID        FROM [Employee]        GROUP BY [FirstName],                  [LastName],                  [Country]    );----------------------
Question 1: SQL Query to find the second highest salary of Employee
Answer: There are many ways to find the second highest salary of an Employee in SQL, you can either use SQL Join or Subquery to solve this problem. Here is an SQL query using Subquery:

SELECT MAX(Salary) FROM Employees1 WHERE Salary NOT IN (select MAX(Salary) from Employees ); 


Question 2: SQL Query to find Max Salary from each department.
Answer: You can find the maximum salary for each department by grouping all records by DeptId and then using MAX() function to calculate the maximum salary in each group or each department

SELECT department_id, MAX(Salary) FROM Employees1  GROUP BY department_id
Question 8: Write an SQL Query to find the name of an employee whose name Start with ‘M’
Answer : 
SELECT * FROM Employees WHERE EmpName like 'M%';


Question 10: Write an SQL Query to find the year from date.
Answer:  Here is how you can find Year from a Date in SQL Server 2008 
SELECT YEAR(GETDATE()) as "Year";


Question 11: Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
Answer: You can use the following query to select distinct records:
SELECT * FROM emp a 
WHERE rowid = (SELECT MAX(rowid) 
FROM EMP b 
WHERE a.empno=b.empno)

to Delete:
DELETE FROM emp a 
WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);



https://www.guru99.com/sql-interview-questions-answers.html


https://www.interviewbit.com/sql-interview-questions/












Comments

Post a Comment

Popular Posts