SQL BASICS – Create a Database & Tables
Download and Install SQL\SQL Express
https://www.microsoft.com/en-gb/download/details.aspx?id=101064
Download an Install SQL Server Management Studio
https://aka.ms/ssmsfullsetup
Connect to your SQL Installation with SQL Management Studio
Sever Type: Database Engine
Server Name: Enter your server name or select from the drop-down menu
Click ‘Connect’
You will be connected to the Server. At this point, if you have not created any databases, you will only have the generic databases.
To check, expand the Databases folder
Create a Database
Press CTRL+N to open the new query window.
Type into the new Query window.
Create Database KBTECHPRO
Click to execute button or press F5 to run the SQL Query command.
in the query results window below, you should see ‘database created successfully’
Click on the Refresh button.
You should now see your database.
The database can’t really be used for data entry\reading until you create tables. In the tables you then need to create columns to hold the data.
Create Tables and Columns in your database
Each column in the table should have a datatype and a column width.
Data Types (Important)
https://www.w3schools.com/sql/sql_datatypes.asp
Click on New Query or press CTRL N on your keyboard.
To the right of the object explorer pane a new query window will open
Pay attention to what database you are working in.
Here it shows what database you are working in. You can select the correct database from the dropdown menu or you can specify the USE command in your SQL commands.
This command will use the database, KBTECHPRO and create a table named customers with columns with a column width of 255 characters.
USE KBTECHPRO
CREATE TABLE CUSTOMERS (
Company_Name varchar(255),
First_Name varchar(255),
Last_Name varchar(255),
Customer_Address varchar(255),
Customer_Email varchar(255),
Customer_SLA varchar(255),
Customer_Pricing varchar(255),
Contract_Date date,
)
Press F5 or click execute to run the QUERY\Command
Click the + beside the database to expand it.
Click the + beside Tables and you will see the CUSTOMERS Table.
Click the + beside Columns to view the Columns in the Table.
Notice, the Column names, the datatype and table width.
Viewing Data in the Tables
Right click on dbo.CUSTOMERS
Click on ‘Select Top 1000 Rows’
A new Query will open and execute a SQL query to display the data.
We don’t have any data entered into this database yet so all you can see below is the empty columns in the table.
Note, this command is only displaying the TOP 1000 rows. If you are looking to see all data you could run the following command.
SELECT [Company_Name]
,[First_Name]
,[Last_Name]
,[Customer_Address]
,[Customer_Email]
,[Customer_SLA]
,[Customer_Pricing]
,[Contract_Date]
FROM [KBTECHPRO].[dbo].[CUSTOMERS]
Editing\Adding data
Right-Click on the Table dbo.customers and select ‘edit top 200 rows’
You will see ‘NULL’ in each column. This means there is no data in that column.
Click in each field, enter your data.
You may notice a RED circle with a white exclamation mark. Continue to enter your data. This is telling you that data has been changed in the column but not yet committed. Complete filling out data in the field and press enter.
Click the X to close each SQL Query Tab
Close the open tabs.
Click the X to close each SQL Query Tab
Right click on dbo.CUSTOMERS
Click on ‘Select Top 1000 Rows’
Here you will notice the data you have entered.
