Skip to main content
Examples of SQL Queries
Once data has been uploaded onto a SQL database it can be queried in a number of different ways. Here are some example queries. Note that some queries may not run on a particular relational database engine.

Find identifiers of a given type (typedefs, in this case):
select name from
ids left join tokens on ids.eid = tokens.eid
where ids.typedef = true

Number of different files that use a given identifier:
select name, count(*) as cf from (
select fid, tokens.eid, count(*) as c from
tokens
group by
eid, fid) as cl inner join ids on
cl.eid = ids.eid
group by ids.eid, ids.name
order by cf desc;

Number of times an identifier occurs in a single file:
SELECT IDS.NAME AS INAME, FILES.NAME AS FNAME, COUNT(*) AS C FROM TOKENS
INNER JOIN IDS ON
IDS.EID = TOKENS.EID
INNER JOIN FILES ON
TOKENS.FID = FILES.FID
GROUP BY IDS.EID, TOKENS.FID
ORDER BY C DESC;

Number of times an identifier occurs in the workspace:
select name, count(*) as c from tokens
inner join ids on
ids.eid = tokens.eid
group by eid
order by c desc

Reconstitute the file with fid = 4:
select s from
(select name as s, foffset from ids inner join tokens on
ids.eid = tokens.eid where fid = 4
union select code as s, foffset from rest where fid = 4
union select comment as s, foffset from comments where fid = 4
union select string as s, foffset from strings where fid = 4
)
order by foffset
The result will have newlines in the wrong places. Piping the output through a shell script like the following can fix this problem.
sed -e '/^[0-9][0-9]* rows/d' |
tr -d '\n' |
sed 's/\\u0000d/\
/g'
The above script will massage the HSQLDB output removing the trailing N rows line and all existing newlines, and changing the embedded \u0000d sequences into newlines. For the Windows line-end conventions the same script would be:
sed -e '/^[0-9][0-9]* rows/d' |
tr -d '\n\r' |
sed 's/\\u0000d\\u0000a/\
/g'

Show the projects each identifier belongs to:
select IDS.NAME, PROJECTS.NAME from IDS
INNER JOIN IDPROJ ON IDS.EID = IDPROJ.EID
INNER JOIN PROJECTS ON IDPROJ.PID = PROJECTS.PID
ORDER BY IDS.NAME;

Show the included files required by other files for each compilation unit and project.
select
projects.name as projname,
cufiles.name as cuname,
basefiles.name as basename,
definefiles.name as defname
from
definers inner join projects on definers.pid = projects.pid
inner join files as cufiles on definers.cuid=cufiles.fid
inner join files as basefiles on definers.basefileid=basefiles.fid
inner join files as definefiles on definers.definerid = definefiles.fid;

Speed-up processing:
create index teid on tokens(eid)
create index tfid on tokens(fid)

Obtain identifiers common between files participating in a define/use relationship:
SELECT
tokensa.eid,
min(ids.name) as identifier,
min(filesb.name) as defined,
min(filesa.name) as used
FROM definers
INNER JOIN tokens AS tokensa ON definers.basefileid = tokensa.fid
INNER JOIN tokens AS tokensb ON definers.definerid = tokensb.fid
INNER JOIN ids ON ids.eid = tokensa.eid
INNER JOIN files as filesa ON tokensa.fid = filesa.fid
INNER JOIN files as filesb ON tokensb.fid = filesb.fid
WHERE tokensa.eid = tokensb.eid
GROUP BY tokensa.eid, definerid, basefileid
ORDER BY defined, identifier

Create a function and macro call graph:
SELECT source.name AS CallingFunction, dest.name AS CalledFunction
FROM fcalls
INNER JOIN functions AS source ON fcalls.sourceid = source.id
INNER JOIN functions AS dest ON fcalls.destid = dest.id

Contents « Previous Next (Details of the Collected Metrics) »



Last change: Thursday, June 15, 2006 4:55 pm
Unless otherwise expressly stated, all original material on this page created by Diomidis Spinellis is licensed under a Creative Commons Attribution-Share Alike 3.0 Greece License.

Comments

Popular posts from this blog

SQL Server interview questions along with their answers

1.     What is SQL Server? SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is used to store, retrieve, and manage data efficiently. What is the difference between SQL and SQL Server? SQL (Structured Query Language) is a standard language used to communicate with and manipulate databases. SQL Server is a relational database management system that implements the SQL language.  What are the different types of backups available in SQL Server? SQL Server supports several types of backups, including Full Backup, Differential Backup, Transaction Log Backup, and File or Filegroup Backup. What is normalization in SQL Server? Normalization is the process of organizing data in a database to reduce redundancy and dependency. It helps in improving data integrity and performance.  What is a clustered index? A clustered index determines the physical order of data in a table. A table can have only one clustered index, and it...

Define algorithm and write a note on Complexity and Time- Space Tradeoff with example.

ASSIGNMENT SET -2 Q.1.Define algorithm and write a note on Complexity and Time- Space Tradeoff with example. Answer:- Algorithm is a step-by-step procedure, which defines a set of instructions to be executed in a certain order to get the desired output. Algorithms are generally created independent of underlying languages, i.e. an algorithm can be implemented in more than one programming language. From the data structure point of view, following are some important categories of algorithms − Search − Algorithm to search an item in a data structure. Sort − Algorithm to sort items in a certain order. Insert − Algorithm to insert item in a data structure. Update − Algorithm to update an existing item in a data structure. Delete − Algorithm to delete an existing item from a data structure. Characteristics of an Algorithm Not all procedures can be called an algorithm. An algorithm should have the following characteristics − Unambiguous − Algorithm should be clear a...

Networking interview questions .

What is LAN? LAN is a computer network that spans a relatively small area. Most LANs are confined to a single building or group of buildings. However, one LAN can be connected to other LANs over any distance via telephone lines and radio waves. A system of LANs connected in this way is called a wide-area network (WAN). Most LANs connect workstations and personal computers. Each node (individual computer) in a LAN has its own CPU with which it executes programs, but it also is able to access data and devices anywhere on the LAN. This means that many users can share expensive devices, such as laser printers, as well as data. Users can also use the LAN to communicate with each other, by sending e-mail or engaging in chat sessions. What's the difference Between an Intranet and the Internet? There's one major distinction between an intranet and the Internet: The Internet is an open, public space, while an intranet is designed to be a private space. An intranet may b...