Question

Chapter 2 How to use the Management Studio Before you start the exercises... Before you start these exercises, you need to install SQL Server and the SQL Server Management Studio. The procedures for doing both of these tasks are provided in appendix A of the book. In addition, you'll need to get the Exercise Starts directory from your instructor. This directory contains some script files that you need to do these exercises. Exercises In these exercises, you'll use SQL Server Management Studio to create the MyGuitarShop database, to review the tables in the MyGuitarShop database, and to enter SQL statements and run them against this database. Create the database 1 Start SQL Server Management Studio and open a connection using either Windows or SQL Server authentication. 2 Open the script file named CreateMyGuitarShop.sql that's in the Exercise Starts directory by clicking the Open File button in the toolbar and then using the resulting dialog box to locate and open the file. 3 Execute the entire script by clicking the Execute button in the SQL Editor toolbar or by pressing F5. When you do, the Messages tab indicates whether the script executed successfully. Review the database 4 In the Object Explorer window, expand the node for the database named MyGuitarShop so you can see all of the database objects it contains. If it isn't displayed in the Object Explorer window, you may need to click on the Refresh button to display it. 5 View the data for the Categories and Products tables. 6 Navigate through the database objects and view the column definitions for at least the Categories and Products tables. Enter and run SQL statements 7 Select the MyGuitarShop database from the Available Databases dropdown menu (Crtl + U) to choose it as the default database. 8 Open a new Query Editor window. Then, enter and run this SQL statement: SELECT ProductName FROM Products 9 Delete the e at the end of Product Name and run the statement again. Note the error number and the description of the error. 10 Open another Query Editor window by clicking the New Query button. Then, enter and run this statement:

Answer 1

To Connect to server follows below steps :

server type:Database Engine

Server name:localhost

Authentication:SQL server Aunthentication

Login:sa

Password:*****

The CREATE DATABASE statement creates a new database.

syntax:

CREATE DATABASE database_name;

Example:

CREATE DATABASE MyGuitarShop;

Once the statement executes successfully, you can view the newly created database in the Object Explorer. If the new database does not appear, you can click the Refresh button or press F5 keyboard to update the object list.

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

This statement lists all databases in the SQL Server:

SELECT name FROM master.sys.databases ORDER BY name;

Or you can execute the stored procedure sp_databases:

EXEC sp_databases;

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

create new table in MyGuitarShop database:

create table categories(
CategoryID int primary key,
CategoryName varchar(255) not null
);
  
create table Products(
ProductID int primary key,
CategoryID int not null,
ProductCode varchar(10) not null,
ProductName varchar(255) not null,
Description text not null,
list_price decimal(10,2) not null,
Discount_percent decimal(10,2) not null,
Date_added datetime
constraint Products fk categories
);

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

Select ProductName from Products;

This query shows the ProductName from Product Table.

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

Delete e at the end of product name

SELECT ProductID,

  TRIM(LEADING 'e' FROM ProductName )

FROM Products;

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

select count(*) as NumberOfProducts from Products;

This query gives the total number of products count in this table.