SQL – Structured Query Language, (A programming language designed to manipulate) used to extract data from relational database management systems. Developed in the early 1970s at IBM. It’s a language that is based on relational algebra (a set of mathematical operations that speak to how things are related) like Intersections, Unions, etc.
In 1986, SQL was adopted as an American National Standards Institute (ANSI) standard, min 1987, it was adopted as an International Standards Organization (ISO) Standards. There are different, proprietary versions of the language, however, the differences are very subtle and almost all are identical in basic syntax.
This makes knowledge of Structured Query Language, a highly transferable skill.
Queries: There are pieces of code that retrieve data from database tables.
Data Manipulation or Data Definition operations: are used to create or alter the database itself. Two-dimensional tables have a set of defined relationships among them. Most relational databases are row-orientated, meaning the ideas or items are stored in rows, with columns that describe the attributes or ideas of interest. The idea behind SQL is to extract just the data we want from a database table or set of tables.
SQL- Basic functions
SELECT – Defines which attributes/columns/fields I want to retrieve or calculate
FROM – Identifies the table from which I want to extract information
WHERE – Adds filters that restrict what rows/records are retrieved
GROUP BY – Defines the level of aggregation I want if I’m summarizing data, i.e., like the summary of transactions
HAVING – Adds filter that restricts what aggregated rows/records are retrieved
ORDER BY – Define the sort order of the results
Select and from work together and needed in every single query. All other commands are optional.
Analytical Organization – Roles
There are other people who are as involved in maintaining the data environment that you rely on to be successful. Understanding who these people are and what they do in your organization will help both understand the environment better and help you to identify the relationships you’ll need to build to be most effective. There is a bit of trade between technical skills and contextual acumen. It’s often that resources with more technical skills tend to do the heavy lifting in the data environment, while those with more context interpret data and make decisions. Where analytics are concerned, organizations tend to focus on broad activity classes and the types of outputs produced.
Major functional activities that take place in a real data environment. Some points to note down. The value chain describes a linear pathway that information follows from event to action. However, real organizations are not constructed around information alone:
- Functional (Marketing, Finance), Customer Group (Business is a consumer), line of business (Generators, shop tools).
- Various flavors of specialized skills or knowledge.
Some terms to know
Data Architecture refers to the design of the data environment to meet the needs of the enterprise.
Data Management involves the actual building and maintenance of the data environment.
Reporting enables standard periodic renderings of specific metrics or data relationships.
Ad-hoc Analysis broadly refers to the directed analysis that seeks to answer a specific question, particularly one that is new or infrequent. If we find ourselves doing the same thing repeatedly, we’re really doing reporting.
To be continued….