Wednesday, October 16, 2013

SQL JOIN Queries – How to use SQL JOINS and What do SQL JOINS mean?

SQL JOIN Queries – How to use SQL JOINS and What do SQL JOINS mean?
The work JOIN basically means to link or connect two entities. In SQL this is used to connect / join two tables.
What are the different types of JOINS and how are they different?
TYPE OF JOIN
DESCRIPTION
INNER JOIN
Returns all rows when there is at least one match in BOTH tables
LEFT JOIN
Return all rows from the left table, and the matched rows from the right table
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
 
Example:
Note:
There is an Educational_Purpose_DB on USFLDFBSQL004 where the following queries can be executed
Query:
/* SELECT ALL ROWS FROM STORE_TABLE_A */
SELECT * FROM Store_Table_A
Result:
ID
STORE_FIRST_NAME
1
WAL
2
PUB
3
MA
4
JC
7
BASKIN
 
Query:
/* SELECT ALL ROWS FROM STORE_TABLE_B */
SELECT * FROM Store_Table_B
Result:
ID
STORE_LAST_NAME
1
MART
2
LIX
3
CY
4
PENNY
5
DONALDS
6
DONUTS
 
Query:
/* INNER JOIN */
--SELECT ALL THAT ARE IN COMMON BETWEEN THE TWO TABLES
 
SELECT
--COLUMNS ARE BEING CHOSEN AND ALIAS ARE BEEN ASIGNED
A.ID AS 'PRIMARY KEY IN TABLE A',
B.ID AS 'PRIMARY KEY IN TABLE B',
A.STORE_FIRST_NAME AS 'STORE INITIAL IN TABLE A',
B.STORE_LAST_NAME AS 'STORE INITIAL IN TABLE B'
-- FROM TABLE A DOING AN INNER JOIN WITH TABLE B
FROM Store_Table_A AS A INNER JOIN Store_Table_B AS B
--CONDITION BEING THE PRIMARY KEYS SHOULD BE THE SAME
ON A.ID = B.ID
Result:
PRIMARY KEY IN TABLE A
PRIMARY KEY IN TABLE B
STORE INITIAL IN TABLE A
STORE INITIAL IN TABLE B
1
1
WAL
MART
2
2
PUB
LIX
3
3
MA
CY
4
4
JC
PENNY
 
Query:
/* LEFT JOIN */
--SELECT ALL THAT ARE IN LEFT TABLE AND WHATEVER IS COMMON BETWEEN THE TWO TABLES
 
SELECT
--COLUMNS ARE BEING CHOSEN AND ALIAS ARE BEEN ASIGNED
A.ID AS 'PRIMARY KEY IN TABLE A',
B.ID AS 'PRIMARY KEY IN TABLE B',
A.STORE_FIRST_NAME AS 'STORE INITIAL IN TABLE A',
B.STORE_LAST_NAME AS 'STORE INITIAL IN TABLE B'
-- FROM TABLE A DOING AN LEFT JOIN WITH TABLE B
FROM Store_Table_A AS A LEFT JOIN Store_Table_B AS B
--CONDITION BEING THE PRIMARY KEYS SHOULD BE THE SAME
ON A.ID = B.ID
Result:
PRIMARY KEY IN TABLE A
PRIMARY KEY IN TABLE B
STORE INITIAL IN TABLE A
STORE INITIAL IN TABLE B
1
1
WAL
MART
2
2
PUB
LIX
3
3
MA
CY
4
4
JC
PENNY
7
NULL
BASKIN
NULL
 
Query:
/* RIGHT JOIN */
--SELECT ALL THAT ARE IN RIGHT TABLE AND WHATEVER IS COMMON BETWEEN THE TWO TABLES
 
SELECT
--COLUMNS ARE BEING CHOSEN AND ALIAS ARE BEEN ASIGNED
A.ID AS 'PRIMARY KEY IN TABLE A',
B.ID AS 'PRIMARY KEY IN TABLE B',
A.STORE_FIRST_NAME AS 'STORE INITIAL IN TABLE A',
B.STORE_LAST_NAME AS 'STORE INITIAL IN TABLE B'
-- FROM TABLE A DOING AN RIGHT JOIN WITH TABLE B
FROM Store_Table_A AS A RIGHT JOIN Store_Table_B AS B
--CONDITION BEING THE PRIMARY KEYS SHOULD BE THE SAME
ON A.ID = B.ID
Result:
PRIMARY KEY IN TABLE A
PRIMARY KEY IN TABLE B
STORE INITIAL IN TABLE A
STORE INITIAL IN TABLE B
1
1
WAL
MART
2
2
PUB
LIX
3
3
MA
CY
4
4
JC
PENNY
NULL
5
NULL
DONALDS
NULL
6
NULL
DONUTS
 
Query:
/* FULL JOIN */
--SELECT ALL THAT ARE IN BOTH TABLES AND WHATEVER IS NOT COMMON BETWEEN THE TWO TABLES AS WELL
 
SELECT
--COLUMNS ARE BEING CHOSEN AND ALIAS ARE BEEN ASIGNED
A.ID AS 'PRIMARY KEY IN TABLE A',
B.ID AS 'PRIMARY KEY IN TABLE B',
A.STORE_FIRST_NAME AS 'STORE INITIAL IN TABLE A',
B.STORE_LAST_NAME AS 'STORE INITIAL IN TABLE B'
-- FROM TABLE A DOING AN RIGHT JOIN WITH TABLE B
FROM Store_Table_A AS A FULL JOIN Store_Table_B AS B
--CONDITION BEING THE PRIMARY KEYS SHOULD BE THE SAME
ON A.ID = B.ID
Result:
PRIMARY KEY IN TABLE A
PRIMARY KEY IN TABLE B
STORE INITIAL IN TABLE A
STORE INITIAL IN TABLE B
1
1
WAL
MART
2
2
PUB
LIX
3
3
MA
CY
4
4
JC
PENNY
NULL
5
NULL
DONALDS
NULL
6
NULL
DONUTS
7
NULL
BASKIN
NULL
 
*If the primary key do not exist in the second table [for left, right and full joins], the values will be displayed as NULL

Tuesday, May 14, 2013

How to create a user / add a user in SQL Management Studio / SQL Server

1. Remote onto the server
2. Open SQL Server Management Studio
 
 
 
3. Log onto the SQL Server using windows pass through authentication
 
 
 
4. Once logged onto the SQL Server, scroll to Security > Logins
 
 
 
5. Right click on Logins node and click on New Login
 
 
 
6. FOR ADDING USER WITH WINDOWS AUTHENTICATION FOLLOW THESE STEPS, FOR SQL AUTHENTICATION FOLLOW STEP 7
  • Select General in the Select a Page section and make sure you chose Windows authentication and click on search

  • Once you click on Search, you will be provided with the following window, choose the appropriate location.

  • Add the appropriate user and click ok

  • AFTER THIS STEP PLEASE FOLLOW STEP 8 AND ONWARDS
7. FOR ADDING USER WITH SQL AUTHENTICATION FOLLOW THESE STEPS
  • Select General in the Select a Page section and make sure you chose SQL Server authentication

  • Put in the desired login name and password in password as well as the confirm password section; Passwords should be at least 8 characters.

  • Check Enforce policy; Uncheck Enforce password policy only if you want to turn off account lockout feature on multiple incorrect password attempts


8. Choose the Default database; if known at creation of account or leave the setting to be master
 
 
 
9. Choose User Mappings in the Select a page section and in the Users mapped to this login section, choose the appropriate database. Also in the Database role membership for: , choose the appropriate membership roles.
 
 
 
MORE INFORMATION ON ROLE MEMBERSHIP:

Database-level role name
Description
db_owner
Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin
Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin
Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator
Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin
Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter
Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader
Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter
Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader
Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

IF YOU DO NOT KNOW WHAT ROLE TO CHOOSE, IT IS BEST TO CHOOSE DB_DATAREADER FOR USERS WHO WILL ONY READ DATA; ALSO IT IS BEST TO CHOOSE DB_DATAWRITER FOR USERS WHO WILL ONLY ADD, MODIFY AND DELETE DATA BUT NOT READ.

MOST USER ACCOUNTS ARE CREATED ARE USED FOR BOTH READING AND UPDATING DATA HENCE IN THIS CASE THE USER WILL HAVE TO HAVE DB_DATAREADER AND DB_DATAWRITER.

10. Choose Status in Select a page section; Make sure the permission to connect to database engine is Grant and Login is Enabled
 
 
 
11.Click OK; you are all set!

IN CASE YOU ENCOUNTER THIS ISSUE WHILE CREATING AN USER:



The issue is with the password and these are the rules of the password in SQL server:

The password must meet the Microsoft SQL Server Strong Password requirements:
  • Does not contain all or part of the user's account name
  • Is more than eight characters in length
  • Contains characters from at least three of the following categories:
    • English uppercase characters (A through Z)
    • English lowercase characters (a through z)
    • Base 10 digits (0 through 9)
    • Nonalphabetic characters (for example: !, $, #, %)