SQL CASE Expression
SQL CASE evaluates a list of conditions and returns one of multiple possible result expressions. It helps to provide multiple if conditions type of logic in our SQL Query.
Lets look in to an example :
Consider a Table Orders with some columns
1 2 3 4 5 6 7 8 | Declare Orders Table ( ID int identity (1, 1), Name Varchar(20), Quantity int, Code int, Value int ) |
Lets fetch records with columns ID, Name and Result where ID should be equal to 50. Here Result is obtained from Quantity, Code and value depending up on the Code. If Code is 0 Result is sum of Code+Quantity, If Code is 1 then Result is sum of Code+Value, else Result is Code+100 {if Code is neither 0 nor 1 then Result is Code+100} So depending up on the Code the Result value will be obtained.
Lets write a query which will do this
1 2 3 4 5 6 | SELECT ID, NAME, CASE WHEN CODE = 0 THEN CODE+QUANTITY WHEN CODE = 1 THEN CODE+VALUE ELSE CODE+100 END AS RESULT WHERE ID = 50 |
The above example shows how CASE Expression can be used to attain the records as per the requirement by using Multiple IF conditions, in this ELSE Clause is optional we can have one more WHEN ( WHEN CODE != 0 OR CODE !=1 THEN CODE+100)
Create a database user using “CREATE USER” statement
Simply creating a login will not guarentee access to the sql server database. A login gives access to an instance of SQL Server, but does not have permission to access the databases. To grant a userid to have access to a database, we need to switch to the database, and then use the CREATE USER statement to map the login to a userid.
To create a user in a database – Type and execute the following statements (replacing computer_nameordomain_name with the name of your computer) to grant userid access to the TestDB database.
USE [TestDB]; GO
CREATE USER [userid] FOR LOGIN [computer_nameordomain_name\userid]; GO
Access control to the database engine using “CREATE LOGIN” statement
To access the Microsoft sql server, users require a login. The login can be a windows account, windows group memner account or a sql server login. To grant a user access to a database we need to create a login for the user. The login lets the user connect to the SQL Server. This login must be configured as a user in the specified database and must be granted permission to database objects.
To create a login in a Query editor window of SQL Server Management Studio, type and execute the following code replacing computer_nameordomain_name with the name of your computer or the windows domain. FROM WINDOWS indicates that Windows will authenticate the user. The optional DEFAULT_DATABASE argument connects the user to the TestDB database, unless the connection string indicates another specific database. The semicolon is an optional termination character for a Transact-SQL statement.
CREATE LOGIN [computer_nameordomain_name\userid] FROM WINDOWS WITH DEFAULT_DATABASE = [TestDB]; GO
This authorizes a user name specified in userid, authenticated by your computer, to access this instance of SQL Server.
Read data from table using “SELECT” statement
The SELECT statement is the most common Transact-SQL statement. To read the data in a table – Type and execute the following statements to read the data in the Items table.
-- The basic syntax for reading data from a single table SELECT ItemID, ItemName, Price, ItemDescription FROM dbo.Items GO
An asterisk is used to select all the columns in the table. This is used in ad hoc queries.
-- Returns all columns in the table -- Does not use the optional schema, dbo SELECT * FROM Items GO
A WHERE clause can be used with select statements to limit the rows that are returned to the user.
-- Returns only two of the records in the table SELECT ItemID, ItemName, Price, ItemDescription FROM dbo.Items WHERE ItemID < 60 GO
Values returned in the select queries can be manipulated on the fly. Mathematical operations can be performed on the columns when they are returned. Columns that have been changed in this way will not have a name unless you provide one by using the AS keyword.
-- Returns ItemName and the Price including a 7% tax -- Provides the name CustomerPays for the calculated column SELECT ItemName, Price * 1.07 AS CustomerPays FROM dbo.Items GO
Create new table in Mssql database with “CREATE TABLE” statement
To create a table we can use the Query Editor window of the SQL Server management studio. We must provide the name of the table and names,datatypes of each column in the table. Its useful to indicate whether null values are allowed in each column. Before we create a table, we must indicate the database we intend to use by using the following command.
Example code:
USE TestDB GO
The USE command is used to change the database context to the TestDB database. The default database is the master database. Creating objects in the master database is a bad practise. The syntax for creating a table is illustrated below.
Example code:
CREATE TABLE dbo.Items (ItemID INT PRIMARY KEY NOT NULL, ItemName VARCHAR(250) NOT NULL, Price money NULL, ItemDescription text NULL) GO
Most tables have a primary key, made up of one or more columns of the table. A primary key is always unique and the database Engine will enforce the restriction that primary key values cannot be duplicated in the table.
The columns in the table are named ItemID, ItemName, Price, and ItemDescription. The ItemID column is the primary key of the table. int, varchar(250), money, and text are all data types. Only the Price and ItemDescription columns can have no data when a row is inserted or changed. dbo is the optional element called a schema. The schema is the database object that owns the table. If you are an administrator, dbo is the default schema which is an acronym for database owner.
Create new Mssql databases with “CREATE DATABASE” statement
Transact-SQL statements can be used to access the database engine directly.To create a database we can use the Query Editor window of the SQL Server management studio. The command to create a database is
CREATE DATABASE TestDB GO
Additional help information can be obtained on the complete syntax of the CREATE DATABASE by highlighting the words and then pressing the F1 key. An online help would open up. In Query Editor, pressing the F5 key will execute the statement and create a database named TestDB. When a database is created, SQL Server makes a copy of the model database, and renames the copy to the database name. This operation generally only takes few seconds, unless a large large initial size of the database, as an optional parameter, is specified.
