Musings About Work and Life
Posts related to computing.
This is an easy one, but one I wanted to write down. How to show line numbers in Oracle SQL Developer.
In Oracle SQL Developer go to Tools → Preferences. Within the preferences window navigate to Code Editor → Line Gutter and select Show Line Numbers.
I’ve noticed my posts on how to show line numbers and configure keyboard shortcuts in Oracle SQL Developer seem to get a decent amount of search traffic. So, to expand on the theme I figured it might be valuable to share the same details on customizing SQL Server Management Studio environments. For this, I will keep these details in a single article.
In these examples I am using SQL Server Management Studio version 18.8. This version is freely available to download from Microsoft here. However, these setting have been in the same location of SSMS since I started using MS SQL all these years ago (SQL 2005 Anyone?).
To access the application options in SSMS, navigate to the Tools menu and select Options. Many of the options for customizing SQL Server Management Studio will be found here.
Once in the options screen, in the list on the left navigate to Text Editor → All Languages → General. Toggle the Line Numbers option to enable or disable.
While in the options screen, in the list on the left navigate to Environment → Keyboard → Query Shortcuts. In this screen you can add various keyboard shortcuts to run queries or stored procedures using hotkeys.
By default, SSMS comes with Alt+F1, Ctrl+1 and Ctrl+2 mapped to sp_help, sp_who and sp_lock respectively. I like to add a mapping to Ctrl+F1 for sp_helptext which will retrieve the definition of the highlighted stored procedure or function.
The great thing about keyboard shortcuts is you can highlight text and hit a given hotkey and the highlighted text will be input for the stored procedure/function. For example, sp_helptext requires an object name as input. To query sp_helptext the syntax would look like this: sp_helptext [ @objname = ] 'name'. With the keyboard shortcut, you only need to highlight the text in the editor and hit the hotkey (Ctrl+F1 in this case).
In Short
As you can see in these menus, there are several changes you can make when customizing SQL Server Management Studio for your needs. When working in a new environment these two changes are the ones I make immediately. Enjoy!
While redeploying this site I came across the need to have a simple reporting tool to display SQL queries. Previously I had a SQL utility open on my desktop and would connect and run queries as needed. But I need to be on my home network or open open up the firewall on the server. Both solutions are not ideal so back to the drawing board. Instead it is easy enough to create a webpage using PHP and exposure the queries I use the most.
Luckily I had created such a tool long ago when I had worked on my Twitter scraper in PHP. At the time I had the exact same requirements for viewing data quickly and easily. So I was able to put this together pretty quick. I figured since the tool has multiple uses on any such platform it would be more appropriate to maintain it as a stand alone package.
The tool is essentially a single PHP page backed by a MySQL data table publishes the results of SQL views. Essentially each row in the provided SQL table represents a report. The details of the table contain the name, display name, url, sort order and active flag. The name is used by the PHP page to lookup the report definition in the SQL table. The display name is what the PHP prints in the list of available reports. The URL is what is drawn in the HTML code of the PHP page. This should be a reference to the PHP page with the report name parameter appended, even though it could link to any page desired. The sort order determined the order in which available reports are displayed and the active flag determines if the given report is displayed in the list of reports.
The PHP page will hit the MySQL server to determine if the view exists when a report is called,. The SQL object defined must be a view. If it does exist it will pull the column names from the information schema and build a table header with those names. It will then select all rows from the view and draw the HTML table.
The report pulls the entire view contents so it’s best to limit the number rows returned for performance reasons. There is a CSS file which defines some basic attributes such as font type and size plus background color.
Not much more to it than that. You can find this on my Github page.
My expertise with databases was learned with Microsoft SQL Server. Over the years I learned how to use other DBMS, but Oracle is the only other enterprise level system I have attempted to learn. Although most mainstream database systems follow ANSI standards, there are several nuances to each one which make can make learning the next one challenging. In my experience, going from MS SQL to Oracle proved to be a steep learning curve. Properly assigning values to variables and using the Oracle DUAL Table was part of that learning curve.
One area that is completely different is working with and assigning variables when writing SQL “programs” (TSQL in MS SQL and PL/SQL in Oracle). Using TSQL you can declare a variable using any name you like prefixed with an “@” and followed by a datatype. In PL/SQL you do not prefix variable names with any special characters. In this example I show how to declare and assign a string variable with a value.
Also in Oracle you cannot assign a string directly to a variable like you can in MS SQL. You must “select” the value from the DUAL table which is an automatically generated table in Oracle. More details on the DUAL table can be found here.
In SQL Server
DECLARE @Something VARCHAR(500)
SET @Something = ‘Some Random String’
OR
SELECT @Something = ‘Some Random String’
In Oracle
SpacePath varchar(500)
SELECT ‘Some Random String’ INTO SpacePath FROM dual;
I recently revisited old unpublished blog posts and this was one of them. This was initially outlined in September 2017 using Oracle 12c. In January 2021 the details were elaborated and published.
This is Part 3 of the three part series detailing the ‘tricky SQL test’ I took to get a potential job. Read Part 1 of the series here and Part 2 here.
Given a table events with the following structure:
create table events ( event_type integer not null, value integer not null, time timestamp not null, unique (event_type, time) ): insert into events (event_type, value, time) values (2, 5, '2015-05-09 12:42:00'); insert into events (event_type, value, time) values (4, -42, '2015-05-09 13:19:57'); insert into events (event_type, value, time) values (2, 2, '2015-05-09 14:48:30'); insert into events (event_type, value, time) values (2, 7, '2015-05-09 12:54:39'); insert into events (event_type, value, time) values (3, 16, '2015-05-09 13:19:57'); insert into events (event_type, value, time) values (3, 20, '2015-05-09 15:01:09');
Write an SQL query that, for each event_type that has been registered more than once, returns the difference between the latest (i.e. the most recent in terms of time) and the second latest value. the table should be orderred by event_type (in ascending order).
For example, given the following data:
event_type | value | time ------------+-----------+-------------------- 2 | 5 | 2015-05-09 12:42:00 4 | -42 | 2015-05-09 13:19:57 2 | 2 | 2015-05-09 14:48:30 2 | 7 | 2015-05-09 12:54:39 3 | 16 | 2015-05-09 13:19:57 3 | 20 | 2015-05-09 15:01:09
Your query should return the follow rowset:
event_type | value ------------+----------- 2 | -5 3 | 4
For the event_type 2, the latest value is 2 and the second latest value is 7, so the difference between them is -5.
The names of the columns in the rowset don’t matter, but their order does.
This is Part 2 of the three part series detailing the ‘tricky SQL test’ I took to get a potential job. Read Part 1 of the series here. This one is much easier than Part 1. I got most of the credit for this one, but I couldn’t figure out the group by to limit the number previous results.
Given a table events with the follow structure:
CREATE TABLE camera (
cameraId INT NOT NULL,
warningType INT NOT NULL,
warningLevel INT NOT NULL,
warningTime DATETIME NOT NULL,
UNIQUE INDEX warningTime (warningTime)
);
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 2, 8, '2017-09-13 13:42:00');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 4, -46, '2017-09-13 14:19:57');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 2, 7, '2017-09-13 15:48:30');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (3, 2, 3, '2017-09-13 13:54:39');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 3, 38, '2017-09-13 14:32:36');
Write an SQL query that, for each camera and warning type (cameraId, warningType), returns the most recent value (in terms of time). the table should be ordered by cameraId (in ascending order), and warningType (in ascending order).
For example, given the following data:
cameraId | warningType | warningLevel | warningTime -----------+-------------+--------------+-------------------- 2 | 2 | 5 | 2017-09-13 13:42:00 2 | 4 | -46 | 2017-09-13 14:19:57 2 | 2 | 7 | 2017-09-13 15:48:30 3 | 2 | 3 | 2017-09-13 13:54:39 2 | 3 | 38 | 2017-09-13 14:32:36
Your query would return:
cameraId | warningType | warningLevel ---------+-------------+-------------- 2 | 2 | 7 2 | 3 | 38 2 | 4 | -46 3 | 2 | 3
The names of the columns in the rowset don’t matter, but their order does.
It is quite easy to assign keyboard shortcuts in Oracle SQL Developer to actions in SQL Developer. I recently configured shortcuts to convert text to all UPPER and all lower case.
It is quite easy to change the keyboard shortcuts in Oracle SQL Developer. Just go to Tools → Preferences and navigate to Shortcut Keys in the left side menu. On the right there will be a lengthy list of actions to choose from. For converting to upper/lower case look in the Code Editor category. You will find Convert Selection to Lower and Convert Selection to Uppercase. You can then change the short to the desired key combination.
Not long ago I went out looking for some freelance work to do in my spare time. I found a list of “the top destinations for freelance work.” I picked one of them and signed up. I made it thought the initial screening, then it was on to THE TEST. This is a bit of an “Ohh crap” moment. In technology and I.T. It is common practice to have applicants take tests. You can tell much about your prospective employer by these test. Some of them give you tests relative to the job they are interviewing for. Others give you tests which are unnecessarily difficult. I’ve found employers in the second usually do this for two reasons. One, they want to test how you solve problems and come to conclusions. Two, they are hiring for a low or entry level position. Since people applying for these positions don’t generally have much experience to promote, so they are given difficult tests.
In the first of a three article series I will review some of the tests I’ve encountered. The tests are for SQL. The platform for these particular tests is MySQL.
The first test looks at calculating H-Indexes. Don’t worry, I didn’t know what an H-Index was when I took this test either. If you care to read up before going any further check out H-Indexs according to Wikipedia.
You are given two tables, author and publication, with the following structure:
CREATE TABLE `character` ( `id` INT NOT NULL, `name` VARCHAR(30) NOT NULL, UNIQUE INDEX `id` (`id`) ); CREATE TABLE `location` ( `characterId` INT NOT NULL, `locationName` VARCHAR(50) NOT NULL, `visited` INT NOT NULL, UNIQUE INDEX `IX_Location` (`characterId`, `locationName`) ); insert into `character` (`id`, `name`) values (1, 'Homer'); insert into `character` (`id`, `name`) values (2, 'Marge'); insert into `character` (`id`, `name`) values (3, 'Bart'); insert into `character` (`id`, `name`) values (4, 'Lisa'); insert into `character` (`id`, `name`) values (5, 'Maggie'); insert into `location` (`characterId`, `locationName`, `visited`) values (1, 'Springfield Nuclear Plant', 30); insert into `location` (`characterId`, `locationName`, `visited`) values (1, 'Shelbyville', 12); insert into `location` (`characterId`, `locationName`, `visited`) values (1, 'Moes Tavern', 5); insert into `location` (`characterId`, `locationName`, `visited`) values (1, 'Kwik-E-Mart', 3); insert into `location` (`characterId`, `locationName`, `visited`) values (3, 'Krusty Burger', 0); insert into `location` (`characterId`, `locationName`, `visited`) values (4, 'Springfield Elementary', 2); insert into `location` (`characterId`, `locationName`, `visited`) values (4, 'The Jazz Hole', 2);
Records in the table character represent characters. Records in the table location represents a location visited by a character from the character table. For each character, the number of locations visited is known.
The H-index of a character is the largest number H such that the character has visited at least H locations, each of which has been visited at least H times.
Write a SQL query that returns a table containing all the characters (characterId and name) appearing in the table character and their respective H-indices (hIndex). The table should be ordered by h_index (In descending order), characterId (In descending order).
characterId | characterName | hIndex
-------------+---------------+---------
1 | Homer | 3
15 | Lisa | 2
42 | Bart | 0
24 | Maggie | 0
12 | Marge | 0
In this example, considering the locations visited by Homer, no four locations have been visited four times or more, so Homers’s H-Index has to be lower than 4. However, there are at lest three locations that have been visited three times or more (In fact, there are four such locations: Springfield Nuclear Plant, Shelbyville, Moes Tavern and Kwik-E-Mart). Therefore, Homer’s H-Index is 3.
The author Double has published one title (Single) that has been cited once, so Double’s H-Index is 1.
Lisa has visited two locations (Springfield Elementary and The Jazz Hole) twice, so Lisa’s H-Index is 2.
The remaining characters have not visited anything, so they should not appear in the result table. Or appear with a value of 0.