10/2021

SQL server tools by Datasparc

0  comments

If you use SQL Server database resources, you might rely on SQL Server Management Services (SSMS), installed on your office Windows equipment, to work with those resources. For whatever reason, however, maybe you left the office to join the remote work world. Maybe you use a Mac, a Linux device, a tablet, or a cell-phone, you need access to SQL Server resources in and out of the office, and you never found a clean, simple way to connect to those SQL resources. It would sure help if you could somehow access those SQL databases, without all the configuration hassles, security headaches, and drama. Help is at hand:  DBHawk SQL Server Tools, by Datasparc.

A standard login screen

SQL server tools - DBHawk

opens the main SQL Server Tools page:

SQL server tools - Connect to database

Click Connect to Database to open the available database resources:

SQL server tools - Choose Database connection

We’ll click the MSSQL-Linux-OVH option to open the main panel:

SQL server tools - Object Browser

In the first dropdown at the upper left, we’ll pick the Sales with space database:

SQL server tools - Search Catalog

In the second dropdown at the upper left, we’ll pick the dbo database role:

SQL server tools - Search Table

In the SQL Server Tools panel, we’ll operate in the SQL Server Sales with space database with virtually the same control and capability of SQL Server Management Studio (SSMS):

SQL server tools - View database objects

We’ll build three tables in this database, set up parent - child key relationships, place data in them, and run a query on those tables. First, we’ll build a CUSTOMER table. Drill down to

Tables -> Create New

SQL server tools-Image1

to open the New Table Wizard. In the wizard, we’ll name the table Customers, and keep the default dbo schema in the Table Name dropdown:

SQL server tools-Image2

Click Next to build the columns. Add a customer_id column, with an integer data type. Check Not Null, because this column will serve as the table key column. Click Add Column:

SQL server tools-Image3

Add a customer_name column, with a varchar(50) data type, and click Add Column. The table has the columns we need, so click Next:

SQL server tools-Image4

Finally, click Finish to save the table:

SQL server tools-Image5

We can also build a table with a script. At the top of the SQL Server Tools panel, click the SQL icon to open the asdfsadf query analyzer, place this script

CREATE  TABLE dbo.[Books] (

[book_id] int   NOT NULL,

[book_name] varchar (50)  NULL ,

[book_price] decimal(7, 2))

GO

in the pane as shown, and click the circled Execute icon:

SQL server tools-Image6

In a similar way, build an “intersection” table, that will tie the customer and book information together:

CREATE  TABLE dbo.[Customers_Books] (

[customer_id] int NOT NULL,

[book_id] int   NOT NULL)

 

GO

Referential integrity helps ensure that the data in a database has logical consistency. Here, the Customers_Books intersection table should only refer to records that exist in the Customers and Books tables. For this, first add index keys to the customers and books tables. This script

ALTER TABLE [dbo].[customers] ADD  CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED

(

     [customer_id] ASC

)

GO

will add a key to the customers table. Click Execute:

SQL server tools-SQL Editor

To graphically build an index on the books.book_id column, first click the Books table. Next, click options, and drill down to Indexes:

SQL server tools-Index Browser

Click Add Index to open the New Index window. Build an index named PK_book_id, of type

NONCLUSTERED

Unique

ASC sorting

on the book_id column as shown:

SQL server tools-New Index

Click Add Index to save it. Now, we can add the constraints on the Customers_Books table. This script

ALTER TABLE [dbo].[Customers_Books]

ADD CONSTRAINT [FK_customers_books_customers]

FOREIGN KEY ([customer_id] )

REFERENCES [dbo].[Customers]([customer_id]);

will add the FK_customers_books_customers constraint on the Customers_Books table. This constraint guarantees that all records in the Customers_Books table will have customer_id values that already exist in the Customers table. In SQL Server Tools, we can also create a constraint graphically. To build a constraint between the Customers_books and Books tables - on the book_id column - highlight the Customers_Books table, and click the Options dropdown. Drill down to and click Constraints:

SQL server tools-Constraints

In the Add Constraint dropdown, click Foreign Key:

SQL server tools-Add Constraints

In the next window, we can build the new constraint, with these values:

Name:           FK_customers_books_books

Schema:         dbo

Table Name:     Customers_Books

Column:         book_id

Referenced

Schema:         dbo

Referenced

Table:          Books

Ref Column:     book_id

The window will look like this:

SQL server tools

Click Add Foreign Key to save the key.

Now, we can add rows to the tables. Run this script

INSERT INTO BOOKS (book_id, book_name, book_price)

VALUES     (1, 'Book 1', 4.99),

           (2, 'Book 2', 2.99),

           (3, 'Book 3', 1.99),

           (4, 'Book 4', 3.99)

INSERT INTO CUSTOMERS (customer_id, customer_name)

VALUES     (1, 'Customer 1'),

           (2, 'Customer 2'),

           (3, 'Customer 3')

INSERT INTO Customers_Books(customer_id, book_id)

VALUES     (1, 2),

           (1, 4),

           (3, 3),

           (2, 3)

in a SQL window:

SQL server tools

Now, we can extract information from the Sales_with_space database. This query will build a result set of all books purchased by each customer:

SELECT     C.CUSTOMER_NAME, B.BOOK_NAME

FROM       CUSTOMERS C INNER JOIN

           CUSTOMERS_BOOKS CB ON

           C.CUSTOMER_ID = CB.CUSTOMER_ID

           INNER JOIN BOOKS B ON

           CB.BOOK_ID = B.BOOK_ID

In SQL Server Tools, the query and result set look like this:

SQL server tools

DBHawk SQL Server Tools brings the flexibility of SQL Server Management Studio directly to a web browser. Through any internet-capable device, DBHawk SQL Server Tools supports full operations on a properly configured SQL Server resource. You definitely need this product and this flexibility. Click Demo Request here to schedule a thirty minute product demonstration to learn more.


Tags


You may also like

Try all these features now

Our free trial includes all of the basic features plus premium access