SQL and Excel equivalent commands are now available in the SQL One-Liner page and in the Reference Card spreadsheet, part of the download package.
It means you can now be 100% effective with SQL or Excel , without having any experience. Just search the normal Muppix keywords in the ReferenceCard and the correct one-liner will appear for Linux, SQL or Excel
The Excel Commands are found in the Reference Card spreadsheet , as part of your Muppix download.
SQL insists on clearly defining the various rigorous datatypes but for BigData this is a big limitation, because a huge data clean up needs to first occur before starting to extract data. Less than 20% of BigData is structured into SQL datatypes, the rest is unavailable to SQL.. SQL may be fast, but you spend a lot of time fixing the data to be 100% clean, when the nature of BigData it is so big and constantly growing, it will never be 100% clean. NoSQl enables you to access the entire BigData, but demands a large investment in IT infrastructure. See
https://en.wikipedia.org/wiki/NoSQL . Muppix provides the toolkit to analyse large data on your own laptop , with no costs.
SQL Structure & Muppix Keywords :
SQL contains mydatabase, mytables and mycolumn, mysecondcolumn, myendcolumn. With these new keywords you can run all the sophisticated commands. The table can also be exported to a text file and the Muppix commands can then be used to extract further information.
For example to show the beginning 1000 lines from mytable with mytext in second column, Keywords to search in sqlmuppix.txt are: begin second fixed line column mytext
Find the SQL in the ReferenceCard spreadsheet or by running this in a terminal :
cat sqlmuppix.txt | grep begin | grep second | grep fixed | grep line | grep column | grep mytext
SELECT TOP 1000 * FROM mytable WHERE mysecondcolumn = 'mytext'
To find SQL commands using Muppix Keywords, run the terminal and type:
cat sqlmuppix.txt | grep line | grep mytext | grep second
select line if second column is 'mytext' ## SELECT * FROM mytable WHERE mysecondcolumn = 'mytext'
try out the SQL commands on the web: http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
mytable = Customers, mysecondcolumn=ContactName, mytext='Ann Devon' SELECT * FROM Customers WHERE ContactName='Ann Devon'
Typical SQL monitor:
It means you can now be 100% effective with SQL or Excel , without having any experience. Just search the normal Muppix keywords in the ReferenceCard and the correct one-liner will appear for Linux, SQL or Excel
The Excel Commands are found in the Reference Card spreadsheet , as part of your Muppix download.
SQL insists on clearly defining the various rigorous datatypes but for BigData this is a big limitation, because a huge data clean up needs to first occur before starting to extract data. Less than 20% of BigData is structured into SQL datatypes, the rest is unavailable to SQL.. SQL may be fast, but you spend a lot of time fixing the data to be 100% clean, when the nature of BigData it is so big and constantly growing, it will never be 100% clean. NoSQl enables you to access the entire BigData, but demands a large investment in IT infrastructure. See
https://en.wikipedia.org/wiki/NoSQL . Muppix provides the toolkit to analyse large data on your own laptop , with no costs.
SQL Structure & Muppix Keywords :
SQL contains mydatabase, mytables and mycolumn, mysecondcolumn, myendcolumn. With these new keywords you can run all the sophisticated commands. The table can also be exported to a text file and the Muppix commands can then be used to extract further information.
- There are no real absolute line-numbers in a table
- Different SQL dialects much as possible standard SQL. MSACCESS has many differences, but should be able to easily adapt the line for Ms Access
- Fixed line positions not reliable ie select lines 2 - 88, because SQL sometimes internally changes its line - order
- Select line above or below doesnt make sense in SQL
For example to show the beginning 1000 lines from mytable with mytext in second column, Keywords to search in sqlmuppix.txt are: begin second fixed line column mytext
Find the SQL in the ReferenceCard spreadsheet or by running this in a terminal :
cat sqlmuppix.txt | grep begin | grep second | grep fixed | grep line | grep column | grep mytext
SELECT TOP 1000 * FROM mytable WHERE mysecondcolumn = 'mytext'
To find SQL commands using Muppix Keywords, run the terminal and type:
cat sqlmuppix.txt | grep line | grep mytext | grep second
select line if second column is 'mytext' ## SELECT * FROM mytable WHERE mysecondcolumn = 'mytext'
try out the SQL commands on the web: http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
mytable = Customers, mysecondcolumn=ContactName, mytext='Ann Devon' SELECT * FROM Customers WHERE ContactName='Ann Devon'
Typical SQL monitor:
Additional SQL resources:
SQL JOINS explained:
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
Try out SQL commands directly in
http://www.w3schools.com/sql/
http://sqlfiddle.com
Download Sample databases:
https://northwinddatabase.codeplex.com -- Northwind
http://msftdbprodsamples.codeplex.com -- AdventureWorks
Download SQL Server onto your laptop:
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
Further information on how to install SQL Server & Docker / Azure:
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/21/sql-server-in-windows-containers/
http://venturebeat.com/2016/06/21/microsoft-shows-off-sql-server-in-a-linux-container-docker-datacenter-comes-to-azure-marketplace/
( sometimes SQL server fails to connect on our PC : You need to verify that the SQL Server service is running. You can do this by going to
Start > Control Panel > Administrative Tools > Services,
and checking that the service SQL Server (SQLEXPRESS) is running. If not, start it. )
SQLServer inside a Linux Container:
https://www.infoq.com/articles/sql-server-containers?utm_source=infoqWeeklyNewsletter&utm_medium=WeeklyNL_EditorialContent_devops&utm_campaign=09132016news
Other flavours of SQL databases , available for free on your laptop:
https://sqlite.org -- terrific way way of testing SQL without the hassle of installing a big database engine
https://www.mysql.com/downloads/ -- most popular open source SQL
https://www.postgresql.org -- excellent opensource SQL
https://www.microsoft.com -- Microsoft Access ( not free )
https://www.libreoffice.org/download/download/ -- LibreOffice Base ( the MS Access equivalent database)
SQL on the Mac:
MySQL, PostgreSQL, SequelPro, FirebirdSQL
SQL on the PC : also includes SQL Server
Try your own SQL commands ONLINE
https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in
http://sqlfiddle.com/
https://www.tutorialspoint.com/execute_sql_online.php
http://www.headfirstlabs.com/sql_hands_on/
SQL JOINS explained:
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
Try out SQL commands directly in
http://www.w3schools.com/sql/
http://sqlfiddle.com
Download Sample databases:
https://northwinddatabase.codeplex.com -- Northwind
http://msftdbprodsamples.codeplex.com -- AdventureWorks
Download SQL Server onto your laptop:
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
Further information on how to install SQL Server & Docker / Azure:
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/21/sql-server-in-windows-containers/
http://venturebeat.com/2016/06/21/microsoft-shows-off-sql-server-in-a-linux-container-docker-datacenter-comes-to-azure-marketplace/
( sometimes SQL server fails to connect on our PC : You need to verify that the SQL Server service is running. You can do this by going to
Start > Control Panel > Administrative Tools > Services,
and checking that the service SQL Server (SQLEXPRESS) is running. If not, start it. )
SQLServer inside a Linux Container:
https://www.infoq.com/articles/sql-server-containers?utm_source=infoqWeeklyNewsletter&utm_medium=WeeklyNL_EditorialContent_devops&utm_campaign=09132016news
Other flavours of SQL databases , available for free on your laptop:
https://sqlite.org -- terrific way way of testing SQL without the hassle of installing a big database engine
https://www.mysql.com/downloads/ -- most popular open source SQL
https://www.postgresql.org -- excellent opensource SQL
https://www.microsoft.com -- Microsoft Access ( not free )
https://www.libreoffice.org/download/download/ -- LibreOffice Base ( the MS Access equivalent database)
SQL on the Mac:
MySQL, PostgreSQL, SequelPro, FirebirdSQL
SQL on the PC : also includes SQL Server
Try your own SQL commands ONLINE
https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in
http://sqlfiddle.com/
https://www.tutorialspoint.com/execute_sql_online.php
http://www.headfirstlabs.com/sql_hands_on/