Question

Define a SQL query? What is the difference between SELECT and UPDATE Query? How do you use SQL in SAS?

Answer 1

• Structured query language (SQL) is a programming language designed for getting information from and updating a database. They are used for managing data in relational database management system (RDBMS). Queries take the form of a command language that lets you select, insert, update, find out the location of data, and so forth. There is also a programming interface.
• The UPDATE query is used to update existing records in a table. The SELECT query is used to select data from a database. The result is stored in a result table, called the result-set.
• We use PROC SQL within SAS, sqldf from within R and pandasql from within Python for using SQL sytax. SHOW EXAMPLES

• Python-

SQLAlchemy Library: This allows you to execute raw SQL queries on tables in database present in MySQL-server from python. These also exists SQLAlchemy Expression Language which represents relational database structures and expressions using Python constructs. The expression language improves the maintainability of the code by hiding the SQL language and thus disallowing a mix of Python code and SQL code.

import sqlalchemy

engine =

sqlalchemy.create_engine(‘mysql://root:password@localhost/database_name’)

from sqlalchemy import text

with engine.connect() as con:

rs = con.execute(text(‘SELECT * FROM BigDiamonds limit 1’))

print rs.keys()

print rs.fetchall()

[u’Unnamed’, u’carat’, u’cut’, u’color’, u’clarity’, u’tabl’, u’depth’, u’cert’, u’measurements’, u’price’, u’x’, u’y’, u’z’] [(1L, 0.25, ‘V.Good’, ‘K’, ‘I1’, 59.0, 63.7, ‘GIA’, ‘3.96 x 3.95 x 2.52’, 0.0, 3.96, 3.95, 2.52)]

Answer 2

HI..

SQL is Structured Query Language, which is a computer language for storing, manipulating, and retrieving data stored in a relational database.

SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres, and SQL Server use SQL as their standard database language.

Also, they are using different dialects, such as −

• MS SQL Server using T-SQL,
• Oracle using PL/SQL,
• MS Access version of SQL is called JET SQL (native format) etc.

DIFFERENCE BETWEEN SELECT AND UPDATE
I have a simple SQL query to join 2 tables:

select veh.[YEAR],veh.MAKE,veh.model,veh.TRIMLIST,veh.ICID,vif.[vif #] from [TheVehListBeta2015-04-21] veh
   inner join [vifList2015-05-04] vif
   on veh.[YEAR]=vif.Yr
   and veh.MAKE=vif.make
   and veh.MODEL=vif.model
   and  (TRIMLIST like '%' + vif.Trim + '%' + Convert(nvarchar(10),vif.Drs) + '%' + vif.Body + '%' )
   order by [YEAR],MAKE,model

The result of this query is: 4983 records
If I use the same query to update the number is different. Here is 2 different queries to update I have tried:
(1)

update [TheVehListBeta2015-04-21]  set EVOXID=vif.[vif #]
from [vifList2015-05-04] vif
Where [TheVehListBeta2015-04-21].YEAR=vif.Yr
and [TheVehListBeta2015-04-21].MAKE=vif.Make
and [TheVehListBeta2015-04-21].MODEL=vif.Model
 and  TRIMLIST like '%' + vif.Trim + '%' + Convert(nvarchar(10),vif.Drs) + '%' + vif.Body + '%' 

(2)   Copy Codeupdate [TheVehListBeta2015-04-21]

   set [TheVehListBeta2015-04-21].EVOXID=vif.[VIF #]
   from [TheVehListBeta2015-04-21] veh inner join [vifList2015-05-04] vif 
   on veh.[YEAR]=vif.Yr
   and veh.MAKE=vif.make
   and veh.MODEL=vif.model
   and  (TRIMLIST like '%' + vif.Trim + '%' + Convert(nvarchar(10),vif.Drs) + '%' + vif.Body + '%' )

SAS uses SQL in two different ways – Where statement and Proc SQL. Where statement is one of the most commonly used SAS statements. The concept and syntax, however, were originally adopted from SQL - this is one example that SAS is a powerful language that imports and mixes syntax from other languages. Proc SQL is the main tool within SAS to use SQL. While Proc SQL is a SAS procedure, it performs many functions similar to those found within SAS data steps. Often, for data manipulation, data step or Proc SQL can be used either individually or interchangeably. Four major areas which describe the effective use of SQL in SAS Proc SQL are outlined 

THANKS,

REGARDS 

SRI