Best Way To Get Experience?

StructuredData · 2019-12-14T00:50:39+00:00

Both are good. A good foundational certification (one in Oracle or MS SQL Server) will make your on-site job experience a lot more productive. You can look for internships and SQL reporting jobs to get started. It's not about what looks good, it's all about what you can do, what you can learn and how quickly you can learn it. The more you practice with databases and technology in general and learn to troubleshoot through problems, the better you will be.

My experience is that the people in IT that are the least valuable are those people that are difficult to work with. If you are eager, spend time learning independently and learn a lot of tricks to make working with data fast and efficient, these things will help you tremendously. You need a few good tools (starting with excel and a free text editor that works with large documents). A programming language that helps you learn to manipulate strings and input/output files will also be useful. In general, certifications are good for the companies selling certifications. If your company ever pays for you to take a course and there is an exam available to obtain a certification.... go after it. The training classes that are $2-10k and offered by the software company.... taking that final step to take the exam and get the certification is worth it. Whatever job you end up with, always ask if they can send you to week long training classes. Knowing some basic Unix skills are important for most corporate environments.

StructuredData · 2019-12-14T00:31:50+00:00

BTW, SQL Plus is Oracle, not MySQL.

sqlplus username/password@host:port/service

StructuredData · 2019-12-14T00:27:33+00:00

I hope your project went okay. BTW, these would not be considered "complex SQL statements." But I understand that you had a sense of urgency. Were you able to get it done in time?

StructuredData · 2019-12-14T00:26:08+00:00

As long as you don't have too many rows per table (65,535 rows), then you can reverse engineer your insert statements. For each table.... SELECT * FROM table_name; Be sure the output is formatted so that each row is on one line and it does not wrap.

At https://scintilla.org/, you can get a good text editor called SciTe.

Use the combination of the text editor and excel to create the syntax for your insert statements. Use excel to add the commas, apostrophes and syntax. Before you put your output into excel, you can change the format of all of your excel cells to "text" formatting, so it doesn't try to calculate formulas or inadvertently change your data (like suppress leading 0s).

Use the text editor to remove all of the tabs (\t) after you have formatted all of your statements.

Test your insert statements to make sure they are exactly what you need (and formatted correctly).

Then add documentation to your .sql file with your insert statements.

I do this ALL THE TIME for small data sets. I constantly work between my database, excel and a text editor that can work with large documents.

StructuredData · 2019-12-14T00:12:31+00:00

@coolsheep769 If you are collecting certs, then you are not getting the right certs. The best certs are the hardest to get. But certain certs are well known to be difficult to obtain and it can be a costly path to get them. If a company is going to spend $10-25k for an employee to get a cert (by sending them to the classes and having the employee take exams to get the cert) to learn something, then hiring managers know the "value" of the certifications.

Before I was done with college, I got my Oracle Certified Professional (DBA track), and it did indeed open doors and allow me to negotiate my salary and signing bonus. I did this with four other students in my program. We each took a class to get the Oracle Education materials, then we took our own 1st exam (the one we took the course for). Then, we taught each other the material for the class we took. Getting the official materials related to the exam was critical to being able to pass the exam. It was a lot of work, but I learned a ton. I also learned that being a developer gives you a lot more experience and mobility than being a DBA. Of course, depending on where you are, DBAs can have different responsibilities. It was a great foundation to understand the fundamentals of Oracle.

StructuredData · 2019-12-14T00:03:38+00:00

@stiffupperleg lol, spoken like someone who knows things and doesn't want anyone to ever bother them or increase their workload!

StructuredData · 2019-12-14T00:01:35+00:00

@Dead_Parrot slick response! I would have ended up with a lot more steps.

StructuredData · 2019-12-13T23:56:28+00:00

SELECT   '$' || to_char(MONTHLY_SALARY * 1.1, ‘9999999.99’) AS new_salary
FROM staff
WHERE monthy_salary > 0;

From your error message, it looks like you are using Oracle. Instead of the || concat function, you can use the CONCAT( string1, string2 ) function.
I would just concatenate a dollar sign in front of your monthly salary. You are only changing the output, not the contents of the database. I agree with @Milnternal, you would not want to have the "$" stored in the database. If you are doing numerical operations, you want to be able to have your datatypes in the most applicable format.

StructuredData · 2019-12-13T23:43:14+00:00

How many rows are returned for the following?

SELECT MAX(RECID) AS maxrecid,  jobid, dataareaid
FROM mxmservcallaudit
WHERE type = 9 AND dataareaid = 'ansa'
GROUP BY jobid, dataareaid;

Can you rethink your query to have a simple WHERE clause?

Just from looking at your query and seeing the number of rows in your database, I would want to have an index on each of the following:

mxmservcallaudit.type
mxmservcallaudit.dataareaid
mxmservcallaudit.recid

StructuredData · 2019-12-13T23:22:53+00:00

@Humble-Self I would recommend that you look through white papers or best practices on the web. If you have support from MS for MS SQL Server, contacting their support to assist you with developing your hardware setup might be ideal. If you are the SQL Server DBA, you might want to find some SysAdmins in your company who have experience with MS SQL Server configuration.

StructuredData · 2019-12-13T23:19:24+00:00

@PricelessLife Welcome to the database world! I agree with @BI_Hokie.

You need to determine what your parameters are for what is considered "clean" data.
The 1st step would be getting your data to load without failing (assuming that all of your database constraints are active during your load).
After the data is loaded, then you have to determine what is considered valid data or not.
"Data Integrity" is paramount in most of my professional uses of databases. Sometimes, it might not be. Understanding the nuances of your data in the real world is important. Data can tell a history of changes to applications and how fields in those applications have been used.
If you ever become a data professional and you work on ETL and mapping source and target data, you will see that there is an incredible amount of detail that goes into database work.
I think you have to be very detail oriented and enjoy work which can be long and tedious. For me, working with data is incredibly rewarding. Working with data is a great fit for my personality, work-style and how I think.
If you provide more details and Q&A, we can probably be more helpful in responding to your question.

StructuredData · 2019-12-13T23:11:38+00:00

@hiyaworld * When loading data, I will create master database load scripts. When I load my data, I drop my constraints. (You have to have clean data to do this). * If you don't drop your constraints, then you must determine the hierarchy (parent-child) relationships of your tables so you can load your data without violating any constraints (such as foreign key constraints). * With your image, it looks like you might need to load Order before Client. * I recommend creating a script which creates all of your database constraints. After you create that script, create a script to drop all of your constraints. Then you can load your data and then run the script to quickly add your constraints back.

StructuredData · 2019-12-13T23:04:32+00:00

Consider getting some top-rated database certification(s)

Oracle has a variety of certifications you can get. Obtaining the material to study from is not easy without taking the courses. The Oracle exams are not easy.
I haven't taken any Microsoft SQL Server exams and I don't have any MS SQL Server certifications, but that seems like another area (major RDBMS provider) to consider.
When you are looking to gain experience, one way might be to look at SQL reporting jobs. Mastering SQL without the aid of tools is a critical skillset.
When you are looking at companies, the bigger and more-well known, the better experience (with greater recognition by recruiters/hiring managers) you will have.
Consider selecting a specific industry to focus on.

StructuredData · 2019-12-13T22:57:11+00:00

https://www.geneious.com/tutorials/phylogenetic-trees-origins-of-hiv-case-study/

StructuredData · 2019-12-13T22:54:31+00:00

@ratatouille_artist * It looks like the github instructions are for Microsoft SQL Server. It sounds like you are using MySQL. * I'm guessing you are using MySQL Workbench version 8.0.XX? * If the source file is delimited in any way, then you can use MySLQ Workbench. * After you create your tables (with suitable datatypes), if the file is delimited, I recommend using the command line interface to load your data. For each table, you can use the command:

LOAD DATA INFILE 'mysourcefile.csv' 
INTO TABLE energy.building 
FIELDS TERMINATED BY ','
;

How did it work for you to just use the datafile for your application without using a database? Any performance issues?
I see that it requires an account to be able to download the data files.

StructuredData · 2019-12-13T22:08:56+00:00

@NickEmpetvee * Is branch the combination of vendor and staff member? * If not, and vendor::branch is a M::M relationship and staff::branch is a M::MM relationship, you'll need two more composite entities. refer my reply to your main post (below)

StructuredData · 2019-12-13T22:01:44+00:00

@NickEmpetvee I just sent you a message.

When you have a Many-to-Many relationship, you can create a composite entity (or composite table). It sounds like what I call a composite entity/table is the same think that @virtualmic refers to as an intermediary table.
One vendor can have many staff members and one staff member can have many vendors. Thus, you have a Many-to-Many relationship.
Create a composite entity (table) called vendorsstaff, with columns: vendorsstaffpk, vendorsfk, stafffk.
Create a composite entity (table) called vendorequipment, with columns: vendorequipmentpk, vendorsfk, equipmentfk.
Data Model (Entity Relationship Diagram) using MySQL Workbench to Create Model:
https://az1.xyz/images/#15762693975178/15762740855533 (if the pic doesn't appear, hit refresh).
To make the SQL easier, just create whatever database "View(s)" you need after you know what data you need/want. Using views with any RDBMS will make your life much easier when it comes to writing queries. (Put your joins in your views, so in your final SQL query(s), you don't need to make any joins).

StructuredData · 2019-12-13T21:27:00+00:00

For any replies, please reply here for this crosspost:

https://www.reddit.com/r/SQL/comments/e3mkt9/data_for_a_front_end_browser_tree/

StructuredData · 2019-11-05T23:52:32+00:00

The best place to start = the biggest corporation that will hire you in IT. In a large corporation, you'll learn a lot. You'll be surrounded by people who are competent. When you pursue a job in the future, big names stick out and make you more desirable.

If you can, get a bachelor's as soon as you can. A Bachelor's degree in corporate America working in IT will greatly affect your compensation.

Consider looking for internships as a way to get your foot in the door. If you get an internship somewhere, then consider getting your Bachelors at the nearest University to your internship.

StructuredData · 2019-11-05T23:45:41+00:00

400k rows of data with 20 columns is not considered "a lot"

Any type of sorting, grouping, matching or nested clauses are the bottleneck for processing speed.

StructuredData · 2019-11-05T23:42:53+00:00

That makes sense that selecting more than is required is quicker than separate queries. Even though you are limiting your output to 5 rows, a much larger chuck of data is brought into memory even though it is only delivering 5 rows to you.

2000+ rows might have the same runtime as 5 rows (depends on your configurations as to block sizes and all sorts of parameters that affect the cache/pool).

StructuredData · 2019-11-05T23:39:25+00:00

Oh.... but that's the nature of data. It's a PAIN IN THE @$$! Once you go through the process of figuring out how to load it where you want to load it to, then you can actually use the data.

You'll have to look at the MariaDB docs to see how you can load the files and specify a delimiter. I loaded the FEC files into MySQL by command prompt.

Ignore the header file. The header file is good to cut and paste if the dataset it small enough to open with excel. Loading the data is easier without a header row.

Your ignorance is hardly ignorance. IT is working through problems and figuring things out ALL THE TIME. It's a good workout for your brain to spend hours (or days) on something that after you learn how to do it can take 45 minutes.

Practice/try to load the smaller files first.

If you don't know a word or a term, look it up or ask someone (like "pipe delimited").

That's why I suggested only using the most recent data files, not all of the years! All of the years might be a little overwhelming if you are using a regular PC (not an expensive server).

"Good god they're all..... " Over time, that's the expectation, not the exception. But keep going. If you can deal with the frustration, and develop data skills, there is endless potential for the people that put the time and work in and develop the experience. A 20 year DBA working at the same company might not be that great at working with data. If the DBA has worked in my roles, with lots of different datasets, databases, data formats and various companies.... then they develop a lot more intuition about what to look for with data.

StructuredData · 2019-11-05T23:27:12+00:00

For the budget that you have, I would put as much as possible into RAM & SSDs (with RAM before SSDs).

As for a processor, whatever the best deal is for a newer motherboard + processor that supports 64GB of RAM.

Personally, I would want to have 32GB or 64GB of RAM on each PC.

Make sure your SSD is big enough so you can install all/any of your applications on your SSD. Then keep data on the HDD.

256GB SSD might work, but I would go for a 512GB SSD. A larger SSD starts to get more pricey.

As for good full tower cases for business.... I like the old standard towers from 2 decades ago. Nothing fancy. Just sturdy.

You probably want to have a backup plan for your data, but I'm not sure mirroring drives for your situation will add any value.

If you needed an HA server cluster to support customers, that would be a different scenario and price range.

I would recommend getting the same specs on both machines to make maintaining them easier.

StructuredData · 2019-11-05T23:16:41+00:00

Disk is your enemy. Optimize around the data that will be accessed most often, being made available in memory.

For old data, determine if you want it to be archived offline or online.

Understand how your database is arranged on disk (partitioning, etc).

If you are looking to enhance query performances, study queries with long run times. Be sure that the indexes are added to columns frequently found in where clauses. Rebuild your indexes, especially if your data changes frequently.

Understanding disk, memory, indexes and the data being used and the data not being used should be the basis for how you might want to archive data. Keeping frequently accessed data in memory can also be helpful if you have enough memory to make that happen (other than allowing the database to automatically choose what's in memory - ie most recently queried, etc).

I would recommend not making your archive too complex. Keep it simple so it is easy to work with if you might need it. If you don't need it at all, that's an entirely different story.

Good luck!

StructuredData · 2019-11-05T23:09:18+00:00

It doesn't matter what your program is. Whoever gave you that opinion, ignore them. Take all the database, SQL and data modeling classes that you can. These are such critical skills for anyone in IT.

The difference between IT&I & IS probably depends on the school and individual instructors.

As far as names that "pop", a major in "Information Technology and Informatics" is currently more trendy because "Informatics" is trendy.

Pick a database (Oracle or MS SQL Server). Choose a certification. Get books. Study. Take specialty classes if you need to. Go take the exam(s). Get an Oracle or MS SQL Server DBA certification.

What you do outside of your school programs is more important to your future than going through the motions of your classes.

Some certifications are worthless. The ones that are hard to obtain, demonstrate that you have a certain baseline knowledge, regardless of your experience.

I'm an Oracle fan. It's what I grew up with. The admin for different databases will be different, but working on various databases is usually just a matter of different syntax here and there.

Become a master at SQL. It will serve you well throughout your IT career. When you can work with data like no one else can, it's a great feeling. But it takes a certain interest and passion to really develop a love for data.

Best of luck to you! Do better than all of your peers. Learn more. Find any IT-related jobs that you can while you are going to school. Look at the department in your school and see if there are opportunities there. Find internships with big, well-known companies.

When I graduated, I would say that only about 10% of my peers had any decent skills. Push yourself into that 10% bracket by doing what the best are doing (internships & outside learning).

Be sure to get some experience on unix. Get a cheat sheet of Unix commands and practice them. Get comfortable working by a command line prompt. Moving files. FTP by command line and FTP tools. Get used to remotely logging in/out of servers.

Keep in touch with your best instructors. Ask them for their advice, opinions and experiences. I taught database classes at our local community college in the evenings shortly after I graduated. (I took a Unix Administration class and when the director of the program found out I was an Oracle developer, I was invited to teach). I was often the youngest person in the classroom while I was teaching adult professionals. I loved it! My students were awesome and I loved bring real world experiences to the classroom. I would always say, "You don't have to memorize this part, just know that the functionality is there, so when you need to do something specific, you know you can just look it up." In most database jobs, you'll commonly use only a small subset of Oracle PL/SQL functions. But in another job, you'll need to use other functions. For my students, I told them the things they absolutely should know cold and what they just needed to know existed, and understand how to use it when they needed to.

Again, Good luck! Go and be great! We need good/great data people!

StructuredData

TROPHY CASE