MS SQL TASK:
Create new MS SQL database. In this database, create a table with the following information:
John Smith (age 31): cashier
Mary Sue (age 42): cashier
Mike Presley (age 35): shop assistant
Bill Walker (age 29): guard
Kate Davis (age 26): manager
Execute the following SQL queries and provide the results of their execution in form of screenshots:
-Display the total count of table entrances
-Display all worker positions from this table
-Display all rows from this table which correspond to workers over 30 years of age
-Display the total count of table entrances which correspond to workers under 40 years of age. This time try to give the result a meaningful name.
SOLUTION:
In this sample we are going to create a basic MS SQL database and table in order to execute several queries. We will use the Management Studio distributed with MS SQL Server 2014. Once installed and started, SQL Server Management Studio will greet the user with “Connect to Server” window, where based on the setup configuration the user will be able access Windows Authentication (which we will use for the sake of this example) and alternatively, SQL Server Authentication (which requires inputting login and password).
After connecting to server, we can see Object Explorer window on the left side of SQL Server Management Studio. By opening Databases window we can see the list of existing databases. If no user databases were created, we will only see System Databases (master, model, msdb, tempdb). We can create a new database by executing a new query. A new query tab can be opened by either clicking on the “New Query” icon or pressing Ctrl+N. The query text should look similarly to this:
The parameters used (such as filename and filesize) are subjective to the user and can be modified.
A query can be executed by either clicking on the “Execute” icon or pressing F5. It results in:
We can notice that while the new database was created, its existence is not reflected in Object Explorer. In order to fix this, we should refresh Object Explorer window by pressing Refresh icon or pressing F5 when Object Explorer window is active.
In order for our next queries to interact with this database, we should mark it as the one currently used:
USE Sample
Result:
Next, we should create a new table called “Workers”. The command should also specify the names and types (int for storing digits, char for storing characters/strings) of table’s rows. First row will be used as unique row identifier (id), therefore marked as PRIMARY KEY. Additionally, since we do not plan any of these rows to contain no data, we will mark every of them as NOT NULL. This results in the following query:
CREATE TABLE Workers
(
worker_id int NOT NULL PRIMARY KEY,
worker_name char(50) NOT NULL,
worker_age int NOT NULL,
worker_position char(50) NOT NULL
)
Results:
The rows should be filled with appropriate data:
INSERT INTO Workers(worker_id,worker_name,worker_age,worker_position)
VALUES(1,’John Smith’,31,’cashier’)
INSERT INTO Workers(worker_id,worker_name,worker_age,worker_position)
VALUES(2,’Mary Sue’,42,’cashier’)
INSERT INTO Workers(worker_id,worker_name,worker_age,worker_position)
VALUES(3,’Mike Presley’,35,’shop assistant’)
INSERT INTO Workers(worker_id,worker_name,worker_age,worker_position)
VALUES(4,’Bill Walker’,29,’guard’)
INSERT INTO Workers(worker_id,worker_name,worker_age,worker_position)
VALUES(5,’Kate Davis’,26,’manager’)
Results:
At this point, our table is created and filled with data, which allows us to start working on first out of four required queries, “Display the total count of table entrances” :
SELECT COUNT(*)
FROM Workers
Result:
Second query, “Display all worker positions from this table”, requires us to show the data based on specific column:
SELECT worker_position
FROM Workers
Result:
Third query, “Display all rows from this table which correspond to workers over 30 years of age” requires us to introduce a limit based on data in certain column:
SELECT *
FROM Workers
WHERE worker_age > 30
Result:
The last query is “Display the total count of table entrances which correspond to workers under 40 years of age. This time try to give the result a meaningful name.”
It is a combination of approaches used in several previos queries, with the only new element being the addition of alias to the resulting column:
SELECT COUNT(*) AS workers_under_40
FROM Workers
WHERE worker_age < 40
Result:
SQL Assistance from the Experts
The posted SQL sample was completed by one of AssignmentShark.com writers. If you can’t cope with your technical projects, we will provide online assignment help you to solve any of the issues. Just specify your instructions and receive a completed assignment within a specified deadline.
If you are truly interested in completing IT assignments on your own, you can read one of another SQL code samples on database. Or ask for help with SQL homework one of our experts.