SQL SERVER

How to create a SQL server authentication login ID

How to create a SQL server authentication login ID

SQL Server Authentication is a default mechanism to connect to MS SQL Serverm, but there are times that you need to use SQL Server Authentication to connect to the server, like when you have to connect to a MS SQL Server Database on a different network or when you have a SSPI problem. In order to use SQL Server Authentication, you need to create a SQL Server Authentication Login ID first.

First of all, we will create a new SQL Server account.

1. Run Microsoft SQL Server Management Studio.

2. Expand the Security item in Object Explorer and right-click Logins and choose New Login….

 

3. Enter an account name in the Login name field and choose SQL Server authentication. Then, enter a password for the account.

4. Uncheck the following options to set the password as you want:

• Enforce password policy
• Enforce password expiration
• User must change password at next login

If the options are checked, you should the password according to security policy in Windows.

5. Choose BioStar from the Default database drop-down list.

 

6. Go to the Server Roles page and select sysadmin.

  

7. Go to the User Mapping page and choose BioStar and db_owner.

 

8. Go to the Status page and set Permission to connect to database engine to Grant and Login to Enabled.

  

 9. Right-click the database server in Object Explorer and choose Properties.

 

10. Go to the Security page and choose SQL Server and Windows Authentication mode.

  

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

Course containts
1- create new login (wizard)
2- create new database (wizard)
3- create Table
4- create new user for database
    CREATE LOGIN Yasser_login
    WITH PASSWORD = '123';
    GO

    -- Creates a database user for the login created above.
        CREATE USER Yasser_user FOR LOGIN Yasser_login;
    GO
5- give some garantees for user
   use yasser_db
go
exec sp_addrolemember db_datareader, yasser_user
go
exec sp_addrolemember db_datawriter, yasser_user
go

GRANT select ON dbo.yasser_tab  TO yasser_user;

6- DATATYPES
  char(n) = Fixed width character string. Maximum 8,000 characters
  varchar(n) = Variable width character string. Maximum 8,000 characters
  varchar(max) = Variable width character string. Maximum 1,073,741,824 characters
  text = Variable width character string. Maximum 2GB of text data
  nchar = Fixed width Unicode string. Maximum 4,000 characters
  nvarchar = Variable width Unicode string. Maximum 4,000 characters
  bit    = Allows 0, 1, or NULL
  binary(n)  = Fixed width binary string. Maximum 8,000 bytes
  varbinary  = Variable width binary string. Maximum 8,000 bytes
  varbinary(max) = Variable width binary string. Maximum 2GB
  image    = Variable width binary string. Maximum 2GB
  tinyint = Allows whole numbers from 0 to 255
  smallint = Allows whole numbers between -32,768 and 32,767
  int = Allows whole numbers between -2,147,483,648 and 2,147,483,647
  bigint = Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807
  decimal(p,s)    Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1.
  The p parameter indicates the maximum total number of digits that can be stored (both to the left    and  to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
  The s parameter indicates the maximum number of digits stored to the right of the decimal point. s     must be a value from 0 to p. Default value is 0
7- create view
8- create login by code
   create login yasser_login with password = '123';
   alter login yasser_login with password = '123'
   ALTER LOGIN yasser_login DISABLE;
   DROP LOGIN yasser_login;
   use yasser_db
    go
   CREATE USER yasser_user FOR LOGIN yasser_login;
9- create database by code
10- create table by code
    CREATE TABLE employees
    ( employee_id INT NOT NULL,
      last_name VARCHAR(50) NOT NULL,
      first_name VARCHAR(50),
      salary MONEY
    );
11- create view by code
       use yasser_db
       go
       create view yasser_v as
       select * from yasser_tab
       ;
      ALTER VIEW yasser_v AS
      SELECT *
      FROM yasser_tab
      WHERE id <= 1000;
12- create schema
   USE yasser_db;
   GO
   CREATE SCHEMA yasser_schema;
   GO
-----------
  use yasser_db
  go
  CREATE SCHEMA yasser_schema2 AUTHORIZATION yasser_user;
-----------
  drop schema yasser_schema2;
13- join table to schema
      Right click on the table and choose design and go to properties window in the right side and  
       choose schema and change to schema which u want.
14- select statements
15- order by
16- distinct
17-subquery
18- JOIN
19- group by
20- dates
21- Alter statement
22- Insert into statement
23- delete and drop
24- Update Statement
25- sql functions
26- UNION
-------------

Microsoft Sql Server Course

  SQL stands for Structured Query Language. SQL is used to communicate with a database.

According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.

SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, etc. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete","Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are identified by their

names and are consists of columns and rows. Columns contain the column name, data type,  and any other attributes for the column. Rows contain the records or data for the columns.

What is a SQL Server?

SQL Server is a Microsoft product used to manage and store information. Technically, SQL Server is a “relational database management system” (RDMS). this means two things.

First, that data stored inside SQL Server will be housed in a “relational database”,

Second, that SQL Server is an entire “management system”, not just a database. SQL used to manage and administer the database server.

relational database

mean all data is stored in Relations,

It is tables with rows and columns. Each table is composed of records and each record is identified by a field (attribute) containing a unique value. Every table shares at least one field with another table in 'one to one,' 'one to many,' or 'many to many' relationships. These relationships allow the database user to access the data in almost an unlimited number of

ways, and to combine the tables as building blocks to create complex and very large

databases.

There are three types of relationships between tables. The type of relationship that is

created depends on how the related columns are defined.

One-to-Many Relationship

Many-to-Many Relationships

One-to-One Relationships

difference between most popular database management systems

Feature	Oracle	MySQL	SQL Server
Interface	GUI, SQL	SQL	GUI, SQL, Various
Language support	Many, including C, C#, C++, Java, Ruby, and Objective C	Many, including C, C#, C++, D, Java, Ruby, and Objective C	Java, Ruby, Python, VB, .Net, and PHP
Operating System	Windows, Linux, Solaris, HP-UX, OS X, z/OS, AIX	Windows, Linux, OS X, FreeBSD, Solaris	Windows
Licensing	Proprietary	Open source	Proprietary

1- create new login (wizard)

2- create new database (wizard)

3- create Table

4- create new user for database

    CREATE LOGIN Yasser_login

    WITH PASSWORD = '123';

          GO

          -- Creates a database user for the login created above.

                   CREATE USER Yasser_user FOR LOGIN Yasser_login;

          GO

5- give some garantees for user

   use yasser_db

   go

    exec sp_addrolemember db_datareader, yasser_user

    go

exec sp_addrolemember db_datawriter, yasser_user

go

GRANT select ON dbo.yasser_tab  TO yasser_user;

6- DATATYPES

  SQL Server Data Types

String types:

Data type	Description
char(n)	Fixed width character string. Maximum 8,000 characters
varchar(n)	Variable width character string. Maximum 8,000 characters
varchar(max)	Variable width character string. Maximum 1,073,741,824 characters
text	Variable width character string. Maximum 2GB of text data
nchar	Fixed width Unicode string. Maximum 4,000 characters
nvarchar	Variable width Unicode string. Maximum 4,000 characters
nvarchar(max)	Variable width Unicode string. Maximum 536,870,912 characters
ntext	Variable width Unicode string. Maximum 2GB of text data
bit	Allows 0, 1, or NULL
binary(n)	Fixed width binary string. Maximum 8,000 bytes
varbinary	Variable width binary string. Maximum 8,000 bytes
varbinary(max)	Variable width binary string. Maximum 2GB
image	Variable width binary string. Maximum 2GB

Number types:

Data type	Description
tinyint	Allows whole numbers from 0 to 255
smallint	Allows whole numbers between -32,768 and 32,767
int	Allows whole numbers between -2,147,483,648 and 2,147,483,647
bigint	Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807
decimal(p,s)	Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
numeric(p,s)	Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
smallmoney	Monetary data from -214,748.3648 to 214,748.3647
money	Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807
float(n)	Floating precision number data from -1.79E + 308 to 1.79E + 308. The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.
real	Floating precision number data from -3.40E + 38 to 3.40E + 38

Date types:

Data type	Description
datetime	From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds
datetime2	From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds
smalldatetime	From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute
date	Store a date only. From January 1, 0001 to December 31, 9999
time	Store a time only to an accuracy of 100 nanoseconds
datetimeoffset	The same as datetime2 with the addition of a time zone offset
timestamp	Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable

Other data types:

Data type	Description
sql_variant	Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier	Stores a globally unique identifier (GUID)
xml	Stores XML formatted data. Maximum 2GB
cursor	Stores a reference to a cursor used for database operations
table	Stores a result-set for later processing

7- create view

8- create login by code

   create login yasser_login with password = '123';

   alter login yasser_login with password = '123'

   ALTER LOGIN yasser_login DISABLE;

   DROP LOGIN yasser_login;

   use yasser_db

    go

   CREATE USER yasser_user FOR LOGIN yasser_login;

9- create database by code

10- create table by code

    CREATE TABLE employees

    ( employee_id INT NOT NULL,

      last_name VARCHAR(50) NOT NULL,

      first_name VARCHAR(50),

      salary MONEY

    );

Lesson 4:

use yasser_db;

select * from dbo.employees;

go

alter table dbo.employees add constraint employees_pk primary key (employee_id);

SQL CONSTRAINT

 In SQL, we have the following constraints:

• NOT NULL - Indicates that a column cannot store NULL value
• UNIQUE - Ensures that each row for a column must have a unique value
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE
• FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
• CHECK - Ensures that the value in a column meets a specific condition
• DEFAULT - Specifies a default value for a column

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

ALTER TABLE Customer
ADD CONSTRAINT customer_uq UNIQUE (Id,LastName)

ALTER TABLE Persons
DROP CONSTRAINT customer_uq

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID;

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id)
REFERENCES customer(Id);

ALTER TABLE customer
ADD CONSTRAINT customer_chk CHECK (Id>0 AND City='Sandnes');

ALTER TABLE customers

ADD CONSTRAINT customers_def DEFAULT 'Yasser' for name;

To browse all constraints in the database:

use yasser_db;

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

alter table customers

add cus_add varchar(50);

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

ALTER TABLE Persons
DROP COLUMN DateOfBirth

CREATE INDEX customerIndex
ON customer (LastName, FirstName);

DROP INDEX table_name.index_name;

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

Lesson 5

Auto-increment

allows a unique number to be generated when a new record is inserted into a table.

CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.

In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

Tip: To specify that the "ID" column should start at value 10 and increment by 5, change it to IDENTITY(10,5).

11- create view by code

       use yasser_db

       go

       create view yasser_v as

       select * from yasser_tab

       ;

      ALTER VIEW yasser_v AS

      SELECT *

      FROM yasser_tab

      WHERE id <= 1000;

DROP VIEW view_name

12- create schema

   USE yasser_db;

   GO

   CREATE SCHEMA yasser_schema;

   GO

-----------

  use yasser_db

  go

  CREATE SCHEMA yasser_schema2 AUTHORIZATION yasser_user;

Create table inside schema:

 Create table schema_name.table_name

To select all contents of schema:

SELECT * FROM INFORMATION_SCHEMA.TABLES  ;

-----------

  drop schema yasser_schema2;

13- join table to schema

    Right click on the table and choose design and go to properties window in the right    

    side and choose schema and change to schema which u want.

14- select statements

        Select columns  From table Where conditions;

     SELECT TOP 3 * FROM Customers;

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

Operator	Description
=	Equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

The SQL AND & OR Operators

The AND operator displays a record if both the first condition AND the second condition are true.

The OR operator displays a record if either the first condition OR the second condition is true.

SELECT * FROM Customers
WHERE City='Berlin'
OR City='München';

Lesson 6

---------

SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');

SELECT * FROM Customers
WHERE City LIKE 's%';

15- order by

       SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

16- distinct

     SELECT DISTINCT column_name,column_name
FROM table_name;

17-subquery

    select * from employees

where employee_id = (select max(employee_id) from employees);

SQL Aliases

are used to temporarily rename a table or a column heading.

18- JOIN

      used to combine دمج rows from two or more tables.

   Different SQL JOINs

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
  SELECT o.Order_no, C.Cus_Name, O.price
• FROM Orders o
• INNER JOIN Customers c
• ON O.cus_no=C.cus_no;

Lesson 7:

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
       SELECT o.Order_no, C.Cus_Name, O.price

       FROM Orders o

       left JOIN Customers c

       ON O.cus_no=C.cus_no;

• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables

19- group by

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

20- dates

  SQL Server Date Functions

The following table lists the most important built-in date functions:

Function	Description
GETDATE()	Returns the current date and time
DATEPART()	Returns a single part of a date/time
DATEADD()	Adds or subtracts a specified time interval from a date
DATEDIFF()	Returns the time between two dates
CONVERT()	Displays date/time data in different formats

use yasser_db;

select getdate();

select DATEPART(yyyy,order_date),cus_no,order_no from orders

go

SELECT order_no,Order_Date,DATEADD(day,3,Order_Date) AS OrderPayDate

FROM Orders;

Lesson 8:

You can make difference between 2 dates by day or months or year or any:

select DATEDIFF(day,order_date,GETDATE()) from orders;

Value	Description
data_type(length)	Specifies the target data type (with an optional length)
expression	Specifies the value to be converted
style	Specifies the output format for the date/time (see table below)

The style value can be one of the following values:

Without century	With century	Input/Output	Standard
-	0 or 100	mon dd yyyy hh:miAM (or PM)	Default
1	101	1 = mm/dd/yy 101 = mm/dd/yyyy	USA
2	102	2 = yy.mm.dd 102 = yyyy.mm.dd	ANSI
3	103	3 = dd/mm/yy 103 = dd/mm/yyyy	British/French
4	104	4 = dd.mm.yy 104 = dd.mm.yyyy	German
5	105	5 = dd-mm-yy 105 = dd-mm-yyyy	Italian
6	106	6 = dd mon yy 106 = dd mon yyyy	-
7	107	7 = Mon dd, yy 107 = Mon dd, yyyy	-
8	108	hh:mm:ss	-
-	9 or 109	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Default + millisec
10	110	10 = mm-dd-yy 110 = mm-dd-yyyy	USA
11	111	11 = yy/mm/dd 111 = yyyy/mm/dd	Japan
12	112	12 = yymmdd 112 = yyyymmdd	ISO
-	13 or 113	dd mon yyyy hh:mi:ss:mmm (24h)	Europe default + millisec
14	114	hh:mi:ss:mmm (24h)	-
-	20 or 120	yyyy-mm-dd hh:mi:ss (24h)	ODBC canonical
-	21 or 121	yyyy-mm-dd hh:mi:ss.mmm (24h)	ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset
-	126	yyyy-mm-ddThh:mi:ss.mmm (no spaces)	ISO8601
-	127	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)	ISO8601 with time zone Z
-	130	dd mon yyyy hh:mi:ss:mmmAM	Hijiri
-	131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri

---

Example

The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

The result would look something like this:

Nov 04 2014 11:45 PM
11-04-14
11-04-2014
04 Nov 14
04 Nov 2014
04 Nov 2014 11:45:34:243

21- Alter statement

      Ater table table_name

       Add column_name data_type;

22- Insert into statement
       insert into orders(cus_no,order_no,price)

values

(1,4,5);

select * from orders;

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;

23- delete and drop
       DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';

DELETE FROM table_name;

or

DELETE * FROM table_name;

24- Update Statement

    Update table_name

      Set column_name = value

     Where condition

25- sql functions

     SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• First  () - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

SELECT AVG(Price) AS PriceAverage FROM Products;

SELECT COUNT(*) FROM table_name;

SELECT TOP 1 CustomerName FROM Customers
ORDER BY CustomerID ASC;

Top 1 mean bring top one record.

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;

SELECT MAX(column_name) FROM table_name;

SELECT MIN(column_name) FROM table_name;

SELECT SUM(column_name) FROM table_name;

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

SELECT UPPER(column_name) FROM table_name;

SELECT LOWER(column_name) FROM table_name;

SELECT SUBSTRING(column_name,start,length) AS some_name FROM table_name;

SELECT SUBSTRING(cus_name,2,3) AS some_name FROM customers;

SELECT LEN(column_name) FROM table_name;

SELECT LEN(cus_name) FROM customers;

SELECT ROUND(column_name,decimals) FROM table_name;

SELECT cus_no, ROUND(Price,0) AS orders_price

FROM orders;

SELECT cus_no,GETDATE()

from orders;

SELECT cus_no,convert(varchar(50),GETDATE(),110)

from orders;

26- UNION

The SQL UNION operator combines the result of two or more SELECT statements.

---

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

select city_name

from city_sup

union

select city_name

from city_cus;

result from both of table with eliminate the duplication.

select city_name

from city_sup

union all

select city_name

from city_cus;

result from both of table with the duplication.

select city_name

from city_sup

intersect

select city_name

from city_cus;

result show the common row only;