Database skills are among the most wanted skillset in aprogramming jobas it helps in storing the data to make it easy for users to access it whenever required. Database Administrators work on a chunk of data that can be optimised easily using the rightprogramming language.
The Database Managers who want to grow and expand their IT knowledge base can mend with the data management skills and learn programming languages. To give you a better understanding of the top programming languages that Database Administrators can learn, here is a list to go through.
#1. Structured Query Language [SQL]
#2. PHP
PHP is a well-known platform that is used to develop interactive and complex websites. It also has a strong connection to databases. Developers can easily use PHP to develop a database using it as a server-side scripting language to establish strong connectivity for writing, storing, and retrieving data efficiently. One of the top content management systems - WordPress - is also developed using PHP that streamlines the web development with databases.
#3. Python
Python is a general-purpose programming language that is used for several reasons including database management, system management, and integration with the help of plug-in functionalities and frameworks. It can help in displaying data, perform data analysis, storage, increasing support, eliminating development time, and increasing support.
#4. R
If the objective of thedatabase programminglanguage is to data analysis, statistical software, data mining, and statistical computing, then R is the right platform for you. This platform can work with complex and datasets while working on computational requirements and handle the performance involving mathematical equations that can display, analyze, and store information.
#5. C#
The programming language that is famous for a long time is C-suite that is efficient and flexible for development and runs efficiently on system hardware. It is an ideal platform for cross-platform support and can also be used for the relational database to maximize resources of system and server hosting of data. The native apps can help in high-performance for the intricate nature of C++, C, and C#.
The database is a vital part of the development process and hence the knowledge of any or all of the above-mentioned programming languages is essential for database administrators, data scientists, data analysts, etc. These are the major programming languages that one can use to develop and manage the database and increase their abilities to enhance the chances of employability in the future.
Data definition languages, which are used to declare schemas [perhaps including application-specific integrity constraints]
2.Data manipulation languages, which are used to retrieve and manipulate the stored data
Data manipulation languages can be further categorized as follows:
1.Query languages, which most often are high-level, declarative, and computationally incomplete [i.e., not capable of expressing certain computations, typically due to the lack of support for updates or for recursion or iteration]
2.Procedural languages, which most often are low-level, imperative, and computationally complete
Besides these, most DBMSs provide interfacing mechanisms with which developers can implement applications in a general-purpose language [referred to in this context as a host language] and use the latter to invoke whatever operations are supported over the stored data [because they are part of the behavior of either the data types made available by the data model or the application types declared in the application schema].
III.A Relational Database Languages
SQL is the ISO/ANSI standard for a relational database language. SQL is both a data definition and a data manipulation language. It is also both a query language and capable of expressing updates. However, SQL is not computationally complete, since it offers no support for either recursion or iteration. As a consequence, when it comes to the development of applications, SQL is often embedded in a host language, either one that is specific to the DBMS being used or a general purpose language for which a query language interface is provided.
Figure 6 shows how the schema illustrated in Fig. 1 can be declared in SQL. Notice that SQL includes constructs to declare integrity constraints [e.g., a referential integrity on from DNA_sequence to organism] and even an action to be performed if it is violated [e.g., cascading deletions, by deleting every referring tuple when a referenced tuple is deleted].
FIGURE 6. SQL declarations for the schemas in Fig. 1.
SQL can also express insertions, deletions and updates, as indicated in Fig. 7. Note in Fig. 7 that on insertion it is possible to omit null values by listing only the attributes for which values are being supplied. Note also that the order of insertion in Fig. 7 matters, since referential integrity constraints would otherwise be violated. Finally, note that, because of cascading deletions, the final statement will also delete all tuples in DNA_sequence that refer to the primary key of the tuple being explicitly deleted in organism.
FIGURE 7. Using SQL to effect state transitions in the relation instances from Fig. 2.
After the operations in Fig. 7 the state depicted in Fig. 2 will have changed to that shown in Fig. 8.
FIGURE 8. Updated relation instances after the commands in Fig. 7.
As a relational query language, SQL always returns results that are themselves relation instances. Thus, the basic constructs in SQL cooperate in specifying aspects of the schema as well as the instantiation of a query result. Roughly, the SELECT clause specifies the names of attributes to appear in the result, the FROM clause specifies the names of relations contributing data to the result, and the WHERE clause specifies, in terms of the relations [and their attributes] mentioned in the FROM clause, the conditions which each tuple in the result must satisfy. SQL queries tend to be structured around this combination of SELECT, FROM and WHERE clauses. Figure 9 shows example SQL queries.
FIGURE 9. Using SQL to query the database state in Fig. 9.
QueryRQ1 in Fig. 9 returns a unary table, each tuple of which records the organism_id of organisms that share the common_name of “white_clover.” Query RQ2 returns a binary table relating each common_name found in the organism table with the protein_ids produced by their identified genes. Figure 10 shows the relations instances returned by RQ1 and RQ2.
FIGURE 10. Results of the SQL queries in Fig. 9.
SQL also supports aggregations [e.g., COUNT and AVG, which, respectively, count the number of tuples in a result and compute the average value of a numeric attribute in the result], groupings, and sorting.
Embedding SQL into a host language is another approach to retrieving and manipulating data from relational databases. Vendors typically provide a host language for SQL of which the fragment in Fig. 11 is an illustration. The fragment in Fig. 11 uses a CURSOR construct to scan the organism relation instance for organisms with no common name. When one is found, rather than leaving the value unassigned, the program uses the UPDATE construct to set the common_name attribute to the string None.
FIGURE 11. Embedding SQL in a host language to effect state transitions.
SQL is legally defined by ISO/ANSI standards which are available from those organizations. For comprehensive treatment, a good source is Melton and Simon, 1993. A detailed treatment of the relational algebra and calculi which underpin SQL can be found in Abiteboul et al. [1995].
III.B Object-Oriented Database Languages
In the object-oriented case, the separation between the languages used for data definition, querying and procedural manipulation is more explicit than in the relational case. This is because in object-oriented databases, the syntactic style of SQL is circumscribed largely to the query part of the data manipulation language.
Also, rather than make use of vendor-specific host languages, object-oriented DBMSs either provide interfaces to general-purpose languages or else the DBMS itself supports a persistent programming language strategy to application development [i.e., one in which a distinction between the data space of the program and the database is deliberately not made, which leads to applications that need not explicitly intervene to transfer data from persistent to transient store and back again].
The de facto standard for object-oriented databases is the proposal by the ODMG consortium of vendors. The ODMG standard languages are ODL, for definition, and OQL [which extends the query part of SQL], for querying. The standard also defines interfaces for a few widely used general-purpose languages. Figure 4 could be declared in ODL as shown in Fig. 12.
FIGURE 12. ODL to specify the gene class in Fig. 4.
Note how ODL allows inverse relationships to be named, as a consequence of which referential integrity is enforced in both directions.
Two OQL queries over the gene class in Fig. 12 are given in Fig. 13. Query OQ1 returns a set of complex values, i.e., name-cited pairs, where the first element is the standard_name of an instance of the gene class and the second is the list of strings stored as the value of the citation attribute for that instance. Query OQ2 returns the common_name of organisms associated with genes that have alleles. Note the use of the COUNT aggregation function over a collection value. Note, finally, the denotation g.organism_id.common_name [known as a path expression]. Path expressions allow a navigational style of access.
FIGURE 13. Using OQL to query the gene class in Fig. 12.
For ODMG-compliant DBMSs, the authoritative reference on ODL and OQL is [Cattell et al., 2000]. A more formal treatment of some of the issues arising in object-oriented languages can be found inAbiteboul et al. [1995].
III.C Object-Relational Database Languages
The proposed standard for object-relational database languages is SQL-99. Figure 14 shows how Fig. 4 could be specified in SQL-99. Note the use of ROW TYPE to specify a complex domain, the use of REF to denote tuple identifiers and the use of type constructors such as SET and LIST. Note also that, unlike ODL [cf. Fig. 12], in SQL-99 inverse relationships are not declared. Note, finally, how gene is modeled as including operations, as indicated by the keyword FUNCTION introducing the behavioral part of the specification of gene.
FIGURE 14. SQL-99 to specify the gene entity in Fig. 3.
Two SQL-99 queries over the gene type in Fig. 14 are given in Fig. 15. Query ORQ1 returns a binary table relating the standard_name of each gene with the common_name of organisms where the gene is found. Query ORQ2 returns the common_name of organisms associated with genes that have alleles. Note that in SQL-99 path expressions use the symbol -> to dereference identifiers and [not shown in Fig. 15] the symbol ‘..’ to denote attributes in row types.
FIGURE 15. Using SQL-99 to query the gene type in Fig. 14.
SQL-99 is legally defined by ISO/ANSI standards which are available from those organizations.
View chapterPurchase book
Read full chapter
URL: //www.sciencedirect.com/science/article/pii/B0122274105008449
Temporal Data Types in SQL
Joe Celko, in Joe Celko's SQL for Smarties [Fifth Edition], 2015
12.9.1 Report Period Tables
Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses, so there is no way to get disagreements in the DML.
The report period table gives a name to a range of dates that is common to the entire enterprise.
CREATE TABLE Something_Report_Periods
[something_report_name CHAR[10] NOT NULL PRIMARY KEY
CHECK [something_report_name LIKE ],
something_report_start_date DATE NOT NULL,
something_report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK [something_report_start_date