Information:
Data |
Data is information which comes in different types. Like Names, Age or height. |
Database |
Database is used to store data Systematically, which also makes it easier to handle a lot of data. It is also known as a logical world. |
Database Mangement System |
4 Types - Hierachichal, Network, Relational and Object Oriented (which is the some used to save data in TABLE format). |
Structured Query Language (SQL) |
SQL can be used to INSERT, SEARCH, UPDATE and DELETE database records. SQL also helps optimizing and maintaining databases. |
Installation
Step 1. |
Launch, “SQL Server Installation Center”, through an ISO. Before installing run, “System configuration Checker”, to make sure you do not have any block in installing the software. |
Step 2. |
After test run, go to “installation tab” choose “New SQL stand-alone” to get a complete new SQL Server or the possibility to change existing SQL server. |
Step 3. |
Through setup, make sure to add an administrative account, to access your SQL server. |
Step 4. |
You will now have the proper SQL features and application installed. |
| |
SQL Server Tools: |
Server manager configuration |
Contains many settings like Network configuration settings. used for establishing the services that we expect to be running while the server is up are actually running. |
| |
SQL Server Management Studio |
Install on SQL Server through ISO or browser. System databases are installed for you as you install a new instance of SQL, which will be shown in the management studio. |
Server roles
Sysadmin |
Can perform all activities on the database Engine instance. |
Serveradmin |
Members can perform instance-wide configuration tasks. |
Securityadmin |
Manage instance-level permissions. |
Processadmin |
Can terminate processes running on a database |
Setupadmin |
Can add/remove linked servers from the database. |
Bulkadmin |
Allow the use of BULK INSERT statement on databases hosted on an instance. |
Diskadmin |
Members can manage instance related files. |
Dbcreator |
Create, alter, drop and restore databases hosted in an instance. |
Public |
All logins are members of this role. |
Database roles
DBowner |
Allow all database configuration and management task. |
DBsecurityadmin |
allow the management on membership of fixed and flexible database-level-roles. |
DBaccessadmin |
assign to security principals, who needs to manage database access for logins. |
DBbackupoperator |
allows backup role. |
DBddladmin |
Be able to run any data definition language (DDL) command in the database |
DBdatawriter |
allow modification on a user’s Table such as “insert” and “delete”. |
DBdatareader |
Allow the user to read all data from user tables. |
DBdenydatawriter |
Deny inserting, altering or deleting data from a database’s user tables. |
DBdenydatareader |
deny a user to read data stored within a database’s user tables. |
|
|
Requirements:
| |
Software: |
|
Hardware: |
- .Net 3.5 SP1 is a requirement for SQL Server 2012 |
|
- SQL server 2012 requires minimum 6 GB HDD |
- .Net 4.0 is a requirement that will be installed automatically during feature Installation Step. |
|
- Super VGA (800x600) or Higher resolution |
- Powershell is a requirement |
|
- Minimum Ram 512 MB, recommended 1 GB (for Express edition) - 1 GB, 4 GB recommended (all other editions) |
- Internet explorer 7 or later versions. |
|
- Minimum Processor speed X86 Processor (1.0 GHz), x64 Processor (1.4 GHz) Recommended 2.0 GHz or faster. |
4 types of system databases
Master database |
- Most important out of the four databases. |
| |
- Contains system level information for the instance you’re in. |
| |
- Holds information of other databases you create. |
| |
- Contains the initialization information for the SQL server, without it, the instance can’t start. |
| |
Model Database |
- Act as a template for any database added to your instance |
| |
- All settings, objects, tables and basically all changes will be added to all databases you create. |
| |
Msdb |
- Management system database, has multiple jobs |
| |
- Tracks the SQ server agent which is used to shut your regular jobs like regular backups or data import/export jobs. |
| |
- List all the backups and restores done in the instance. |
| |
- Take regular backups of this database that it contains valuable information |
| |
Tempdb |
- Holds all temporary objects, created by you and by the database itself |
| |
- It will delete all changes and objects when the instance gets restarted. |
Useful SQL Commands
Requesting a single column: |
SELECT "Columnname" |
With " , " you can add more |
FROM "Tablename" |
| |
Request All |
SELECT * |
| |
FROM "Tabel-name" |
| |
writing to Tabel |
INSERT INTO "tabelname" |
| |
VALUES ("Write the value of a given Column) |
| |
Selective search |
SELECT "Columnname" |
| |
FROM "Tabelname" |
| |
WHERE "Columnname" = "Value in column" |
| |
Creating Tabel |
CREATE TABEL "tabelname" |
| |
Delete option |
DELETE FROM "tabelname" |
| |
WHERE "value" |
|
