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
opens the main SQL Server Tools page:
Click Connect to Database to open the available database resources:
We’ll click the MSSQL-Linux-OVH option to open the main panel:
In the first dropdown at the upper left, we’ll pick the Sales with space database:
In the second dropdown at the upper left, we’ll pick the dbo database role:
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):
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
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:
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:
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:
Finally, click Finish to save the table:
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:
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:
To graphically build an index on the books.book_id column, first click the Books table. Next, click options, and drill down to Indexes:
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:
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:
In the Add Constraint dropdown, click Foreign Key:
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:
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:
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:
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.