Can you create a query in MS Access using a SQL Statement?

Microsoft Access is designed such that even if you do not know SQL or SQL syntax you could still use the application. As a consequence, there is no obvious starting point to write a SQL Statement and execute it. However, it does allow you to write a SQL Statement, save the query and execute it.

Open access application. I have just one simple table 'BOOK1'.

In the 'Queries' tab of Create click on Query Design to open a tabbed page 'Query 1" as shown.

Query 1 has two panes. The top one is empty and it is here you can write a SQL Statement and the bottom pane is where you can compose a query using visual elements.

Right-click in the empty top pane to show this drop-down.

Click on 'SQL View'. This view is not the same as 'View' in SQL Server. This opens a text editor where you can write a query. Note that it has just the 'SELECT'. It expects you to write a "SELECT" statement.

Note that there is a Table 1 called 'Book1' shown here.

Now I write a SQL query against this table.

SELECT ID, Field2 

From Book1

Here is the statement in the SQL View's editor pane.

Now save the query, Query1. You need to click on the tab to save as shown.

You can save it with a different name which is usually recommended. I saved it as BookQry as shown.

Now double click on BookQry on the left to open the query to show the result.

Hands-on Learning Event in Honolulu 1 : Introduction to Structured Query Language

A new session is going to start soon. Sign-up here.

Microsoft SQL Azure Database: SQL Server in Microsoft Cloud
Windows PowerShell for SQL Server

My sincere thanks and best wishes to my students in the previous meets of this course.

What kind of data can you work with in DisplayR?

From my previous post you can see that you can really get data from the following kinds of sources:

• Get data by import

    ** SPSS Data Files

    ** SQL Tables

    ** Excel

    ** CSV

    ** Any format that R can handle

SQL tables in SQL Server, SQLite etc are obvious sources of data from relational databases against which you can run SQL Queries.

SQL data is only a small set of data sources which you can use in statistical analysis or author reports.

Here are the various from which you can source your data in DisplayR.

Qulatricss, URL, 

Usage Modulo: How many hours and minutes are there in so many minutes?

x%y is defined as the remainder of the division x/y. You can use modulo to find the minutes and hours in so many minutes.

Let us say we have a cconversion exercise to convert 123490 minutes into minutes and hours.
By long hand we would do this:

123490/60 and find the remainder
We get 2058.1666666667 with 0.1666666667 hours converts to 10
That is 2058 hours and 10 minutes.

We can do the same using modulo as shown.

OR

You can use a much shorter code as shown here: (note input data has changed to 1234901)

How to test a comparison operator in an SQL Query?

Let us say we want to know if a variable x is greater or less than another variable y. How do we write a query to test it?

One way to do this is as follows:

declare @x int
set @x=5
declare @y int
set @y=10
    IF @x < @y
 print 1
    ELSE
 print 0

Hands-on Learning Event in Honolulu 1 : Introduction to Structured Query Language

The above course will be offered again for the fourth time (Oct 4 - Nov 3, 2016) as a non-credit course by the Pacific Center for Advanced Technology Training.

Course summary:

Databases, organized repositories of information, have become indispensable. Knowledge of databases is a must for professionals and in fact even more relevant since the emergence of Big Data in today's world.

In this introductory course you will learn about relational databases and the basics of Structured Query Language (SQL) including sorting; grouping result sets; using DDL, DML, DCL, and TCL. All SQL statements will initially be written for one table. Most practical, modern and relational databases will include a large number of tables and SQL queries have to access information from several tables. This course will then introduce you to querying more than one table. With this skill you will be able to query two or more tables in a database. This is a hands-on course which will take you from installing SQL Server 2012; learning the ins and outs of SQL Server Management Studio and of course a full dose of SQL. You will be both coding as well using Graphical Userr Interface during this training.


During each meeting the students will be assessed for their knowledge, competency and proficiency in SQL.


New this session:

Microsoft SQL Azure Database: SQL Server in Microsoft Cloud
Windows PowerShell for SQL Server
SQL Server 2016

My sincere thanks and best wishes to my students in the previous meets of this course.

How do you run a simple query in HeidiSQL?

It is really no different from running a query in SQL Server Management Studio. You need to have an instance of SQL Server running on your computer. Launch HeidiSQL and get connected to the instance.

You will be seeing this (different in your case) displayed in the HeidiSQL user interface. Either you can get connected to a session you created or a new one using the New button at the bottom of the UI.
 
RunAQuery01.png

You can connect to a single database by selecting it from the list as shown. Here Northwind was selected.

RunAQuery02.png
The Nov21 sessions gets changed and the changed sessions window opens as shown:


RunAQuery03.png

Click on Query to open a window for inserting query related statements as shown:
RunAQuery04.png

In the Query Window insert your SQL statement. For example a query such as the one shown:

SELECT TOP 50 [CategoryName]
      ,[ProductName]
      ,[ProductSales]
  FROM [Northwind].[dbo].[Product Sales for 1997]

 

Click Query and the response will appear below the query window as shown.

 
RunAQuery07.png

The tabbed window in the center has all the necessary tabs like the SAP SQLAnywhere Studio Central.

What is SQL CLR or SQLCLR?

In order to understand what is SQLCLR, you need to know what the individual parts, Sequential Query Language (SQL) and Common Language Runtime (CLR) are.

Common Language Runtime is the programming that manages the execution of programs written in any of the supported languages such as C#, VB, Visual C++ etc to share common object orineted classes. Thiss is somewhat like Java Virtual Machine for programs run from Java. CLR is also called 'managed execution environment'. CLR is therefore related to .NET programming.

SQL is a language that is needed to run queries (post questions to) against SQL Server databases. It is not specific to Microsoft SQL Server. It can also be used with other databases like Oracle, Sybase, etc.
However Transact SQL (T-SQL) is the enhancement to add some progrmmability to SQL.
SQL Common Language Runtime (SQLCLR) is the combo technology (SQL Server and .NET) for hosting .NET common language runtime engine within SQL Server. In other words you can run managed code from within SQL Server.

Since T-SQL already adds to SQL the programming capability why does one need SQL CLR?

The reason I that there are certain useful class of programs that are just not handled by T-SQL, or make it much more complex and less secure. Of course there are more reasons why SQL CLR is needed. This is the reason for the development of SQL CLR. Also Microsoft made extensive integration of SQL Server with its massive .NET programming which makes it lot more flexible.

For, Creating a Visual C++ CLR Console Application in Visual Studio 2015 go here.

What is Google Cloud SQL?

ISO/IEC 27001 compliant Google Cloud SQL is a fully managed database service on a pay-per-use option providing database infrastructure in the cloud. Google Cloud SQL can easily set up, maintain, manage and administer relational MySQL databases in the cloud. Google Cloud SQL is on Google Cloud Platform.

It is so fully automated you may not need a DBA.

There is no software needed for installation or management and ideal for small and mid-sised businesses. Replication, patch management and database management are all automated.

Data is automatically encrypted adn replicated to many geographic
locations and failovers are handled automatically.

Try it free here:
https://console.developers.google.com/freetrial?_ga=1.252176009.1404788957.1438979658

Hands-on Learning Event in Honolulu: Introduction to Structured Query Language

This was offered once I 2012 and once in the beginning of the year and was a total success. It is offered once again to those who could not make it.

Please register at the PCATT.ORG site.

New in 2015: You will also get an introduction to Windows PowerShell. SQL Server 2012 Express will be used.
For details you can also write to:
Hodentek@live.com with course name in the Subject line.

How do you explain this correlated subquery?

The syntax of subqueries is as shown here.

Outer query(some operator) (Inner query)

While non-correlated subqueries are quite easy to understand correlated sub queries
are not quite obvious.

In the case of non-correlated subqueries, the inner query is evaluated first. The value or values returned from inner queries are used in the outer queries based on some operation.

In the case of correlated subquery the outer query is run and for each value returned by the outer query the inner query is evaluated and when the match is found the outer query stops. Usually the inner query just returns either some value or no value and the logical operation is usually Boolean (exists, not exists, any, all, etc.).

The correlated subquery you are asking about is the following posed to the Northwind database:

Use Northwind
Go
Select o.EmployeeID, ShipName, o.OrderID ​
from Orders o​
Where  exists​
(Select i.ShipCity​
from Orders i​
Where i.ShipCity=o.ShipCity and i.EmployeeID in (5,7))
Go

This returns the following result set (here are sme 6 rows out of 674 rows):

CorrelatedSubquery
You are probably intrigued why you are getting employee ID's other than 5 and 7 in
the result.

The reason for this is the inner query result when processed with the logic (exists) returns true or false. If it is true the Orders table returns the columns requested. Inner query result set are not part of the rows returned.

Try the same query replacing 'exists' with 'not exists'. You will find that the subquery returns 156 rows.

The total of all rows returned by the query,

Select o.EmployeeID, o.ShipName, o.OrderID ​
from Orders o​

is 830 which is (156+674).

Can I work on Stored Procedures using LinqPad?

Of course you can. In LinqPad version 4.51.03 you can easily run stored procedures you have created using both SQL and C# Expression.

For example, after connecting to AdvventureWorks2012 database I can see all the stored procedures. Right clicking a stored procedure and choosing StoredProceedureName(...) drop-down, you display the stored procedure in the query window. You need to insert the parameters inside the parenthesis containing the ellipsis and run the query.

Examples are shown in the following two posts:

http://hodentekmsss.blogspot.com/2015/01/learn-querying-sql-server-2012-using.html

http://hodentekmsss.blogspot.com/2015/01/learn-querying-sql-server-2012-using_25.html

What is LINQ?

We know that a query is a question we pose to the database to retrieve some information that we would like to know. For example, in a database that consists of men and women, you may want to know how many men and how many women. Let us go a step further and say that there are people of all nationalities in the database of men and women. You may want to know how many women who hail from Europe are there. This is only filtering of the information but a query can much more complex. This is of course querying a database, let us say a relational database such as the SQL Server where you use the Structured Query Language(SQL), a special language you have to learn.

All data are not in relational databases and they could be in some kind of an XML document, a web service or something else. For example, in order to find some filtered content in an XML document you would use a language called XQuery which is not the same as SQL. This means there is need to learn another query language.

Beginning with Visual Studio 2008 and .NET Framework 3.5 Microsoft introduced LINQ which stands for Language-Integrated Query an innovation that made it possible to query a SQL Server database, an XML document, Web Services etc with a single lanugage and it is a first class query language.

In Visual Studio you can write LINQ in both VB and C# for any collection that supports IEnumerable or the generic IEnumerable interface (or to put it simply a collection in which one can go through the collection item by item).

For example you may have a list  of names ("John", "Tom", "June", "Diana") etc.  or  the number of rows in a database table , etc. and they can be queried using LINQ.

Need to query SQL Server with LinqPad, review the following posts:

http://hodentekmsss.blogspot.com/2014/12/learn-querying-sql-server-using-linq.html
http://hodentekmsss.blogspot.com/2014/12/learn-querying-sql-server-2012-using.html